Index Match/Vlookup with multiple return

[Benfernandez]

Hi Guys,

Tried a few things to get this working but nothing so far; looking to return multiple cells when text is typed into a cell, for example:

Moscow Mule	Vodka	1	**SEARCH TERM**
	Lime	3
	Ginger Beer	1
	Angastora bitter	1

<tbody>
</tbody>

So when Moscow Mule is typed into **SEARCH TERM** the ingredients are returned, different drinks will have different ingredients hence simply returning the four cells to the right of the name will not work, rather it needs to return a block of cells associated with said drink.


Have been attempting index match/vlookup but cant find how to return multiple cells

Thanks in advance!


Ben

 

[BenMiller]

Do you want each ingredient to be returned in its own cell?

 

[Benfernandez]

Yeah preferably, sorry should have meantiond that

 

[BenMiller]

With Moscow Mule in column A, ingredients in column B and search term in C1, put this in C2 and drag down:

=IFERROR(INDEX(B:B,MATCH(C$1,A:A,0)+ROW(A1)-1),"")

 

[Benfernandez]

Thanks, would you be able to explain this quickly? Not quiet sure why iferrror is there, also where would the search term be typed?

 

[BenMiller]

IFERROR is so that if MATCH doesn't find anything, the formula should not evaluate to an #N/A error. Type the formula in C2 and drag it down.