Vlookup for multiple results

[Karnik]

Hi Experts
in given two columns below, attempted to use Vlookup but cell A3 and A6 has same value =8, so only B3 value 21 is picked up and B6 value 26 is not picked up.
Appreciate guidance for modified Vlookup function in following issue

A1 B1
3 16
2 20
8 21
7 22
5 23
8 24
4 25
6 26
1 27


Thanks in advance
Karnik

[Phuocam]

Try this ...

=IFERROR(INDEX($B$1:$B$10,AGGREGATE(15,6,ROW($A$1:$A$10)/($A$1:$A$10=8),ROWS($A$1:A1))),"")

Copy down.

[Karnik]

Hi Phuocam,
Thanks for looking in to, attempted but not getting anything in a cell just blank
may be you can try in
excel 2010 and email file?
Thanks in advance
Karnik

[Phuocam]

See this ...

[FDibbins]

I know you said A and B, so adjust as needed...

	P	Q	R	S
2	3	16		21
3	2	20		24
4	8	21
5	7	22
6	5	23
7	8	24
8	4	25
9	6	26
10	1	27

S2=IFERROR(INDEX(Q:Q,SMALL(IF($P$2:$P$10=8,ROW($P$2:$P$10)),ROWS($A$1:A1))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Then copy down as needed

[Karnik]

Hi phucam & FDibbins
Thanks for solution both re diverse but yield same result
Thanks
Karnik

[Phuocam]

You are welcome!

[FDibbins]

Looks like you gave me a negative rep by mistake? Do you want me to correct tat, and is it possible you gave Phuocam a neg rep by mistake too?