Vlookup with multiple returns

[CassieL]

Hi everyone,

Im trying to vlookup a value from another tab (same workbook) with multiples values return vertically. Please help

For example:

Tab 1
[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD]A[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]111[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]

Tab 2
[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD]A[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]111[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Please help!

 

[James006]

Hi,

If your Tab 1 is named Data, you can use the array function (Ctrl+Shift+Enter) in Sheet 2 cell B2

=IF(ROWS(B$2:B2)<=COUNTIF(Data!$A$2:$A$21,$B$1),INDEX(Data!$B$2:$B$21,SMALL(IF(Data!$A$2:$A$21=$B$1,ROW(Data!$A$2:$A$21)-ROW(Data!$A$2)+1),ROWS(B$2:B2))),"")

HTH

 

[CassieL]

Hi James,

Thanks for replying. Im getting error on the code. It probably my problem on the confusion table

Let say I have data tab with result and lookup value:

Kat Apple
Kat Dog
Kat Tree
Kat Bag
John Car
John Blue

In my second tab, I want to vlookup Kat with result as below:
Vllokup Kat

Results:

Apple
Dog
Tree
Bag

Please help. Thanks!

 

[Aladin Akyurek]

In A1 of Sheet2 enter: Kat

In the formula James006 suggests, replace $B$1 with $A$1.

 

[James006]

Thanks Aladin for correcting the formula ...