vlookup with multiple values

[Legacy 327817]

I need to do a vlookup but there might be multiple values for one lookup.

For example,

Sheet 1 has a value of 123 in cell A1.

Sheet 2 has a table where column A is numbers and column B is the value we want to return.

EX: Sheet 2

Column A......Column B
1) 123..............Apple
2) 456..............Orange
3) 234..............Banana
4) 123..............Kiwi

We want excel to look at cell A1 and pull all the values in sheet 2 (column B) that match. So for this example, excel would pull Apple and Kiwi (cells B1 and B4 in sheet 2).

Any help would be greatly appreciated.

Thank you!

 

[Mike LH]

Hi,

You can pull them all out into a continuous list with this ARRAY formula, see below for how to enter it. Put the formula in a cell and array enter it then drag down


=IFERROR(INDEX(Sheet2!$B$1:$B$100,SMALL(IF(Sheet2!$A$1:$A$100=$A$1,ROW($B$1:$B$100)),ROW(1:1))),"")

This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.

Or if you want them in their original row then put this in b1 and drag down

=IF(Sheet2!A1=$A$1,Sheet2!B1,"")

 

[Aladin Akyurek]

Sheet1

A1: 123

A2, just enter:

=COUNTIFS(Sheet2!A:A,A1)

A3, Sublist

A4, control+shift+enter, not just enter, and copy down:

=IF(ROWS(A$4:A4)<=A$2,INDEX(Sheet2!$B$2:$B$5,
   SMALL(IF(Sheet2!$A$2:$A$5=A$1,
   ROW(Sheet2!$B$2:$B$5)-ROW(Sheet2!$B$2)+1),
   ROWS(A$4:A4))),"")

 

[mick0005]

Hi,

You can pull them all out into a continuous list with this ARRAY formula, see below for how to enter it. Put the formula in a cell and array enter it then drag down


=IFERROR(INDEX(Sheet2!$B$1:$B$100,SMALL(IF(Sheet2!$A$1:$A$100=$A$1,ROW($B$1:$B$100)),ROW(1:1))),"")

Hi Mike,

This is one of the shorter formulas I have seen to accomplish this. I don't understand exactly how it is working though. Can you walk it through a bit to explain?

In particular I have little experience using SMALL and ROW and I don't understand why this isn't working correctly if my ranges were to begin on say, A3 and B3 instead of A1 and B1.