INDEX MATCH comma separated values in one cell

Hello,

I need, what I think is, a complex formula to help return financial data. I'm trying to pull dollar values from a financial sheet with an INDEX MATCH formula and am having difficulty when the "Lookup Value" cell has more than one value in it.

I look at an alternate sheet to get the dollar amount of a device, which, when it's just one, works great. If the cell containing the "lookup value" contains multiple items (separated by a comma), I'm not sure how to do this.

Sheet 1 (Financial Data)

	Col A	Col B	Col C	Col D
Header Row 1	Prod Line	Item#	Std Cost	Avg Selling Price
Row 2	BOK	TRAVEL	$3.39	$4.85
Row 3	BOK	SPACE	$4.22	$5.23
Row 4	BOK	BOAT	$2.92	$5.59
Row 5	BOK	CAR	$3.14	$5.26

Sheet 2 (Results Sheet)

	Col A	Col B	Col C
Row 1	Item#'s	Avg. Std Cost	Avg. Selling Price
Row 2	TRAVEL, SPACE	(Need the average Std cost of both (TRAVEL and SPACE combined))	(Need the average selling price of both (TRAVEL and SPACE combined))

The user enters in the data in Col A / Row 2 ("Item(s) in Sheet 2). I'm using INDEX MATCH currently and it works great with one "Item" in the Item's column. I just can't figure out how to get it to reconcile two items that are comma separated in Col A on sheet 2.

Thank you all in advance!!
John

Can you copy this into a workbook.

See example workbook attached.

Test.xlsx

Try this array formula in B2 and then copy B2 to C2

Please Login or Register  to view this content.

Note that finish with Ctrl + Shift + Enter to use a formula in array mode

WORKS PERFECT!! Thank you very much.

Any way to change this into an AVERAGEIF to incorporate the rejection of any $0 or blanks? I've tried a few iterations but can't get the formula correct.

I try it this way:

Please Login or Register  to view this content.

But get a #VALUE! error

Thanks FDibbins - That's a great alternative!