vlookup with one reference, and return with multiple values

**nth34** · 05-04-2015, 12:47 AM

Hi guys,

I have a problem related to a reference code with several cells in return.
vlookup can only get one result as return.

Is there any method to return multiple values with one reference code?

here is my example and expected result.
thank you guys ..!!

**FDibbins** · 05-04-2015, 01:00 AM

If you are prepared to swing your table around, we can do it this way...

	G	H	I	J
14	D001	D002	D003	D004
15	P001	P008	P006	P110
16	P005
17	P009
18
19

G15=IFERROR(INDEX($E$2:$E$7,SMALL(IF($D$2:$D$7=G$14,ROW($E$2:$E$7)-1),ROWS($A$1:A1))),"")
copied down and across. This is an ARRAY formula...

...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. Press F2 on that cell and try again.

**nth34** · 05-04-2015, 01:40 AM

Originally Posted by FDibbins

If you are prepared to swing your table around, we can do it this way...

	G	H	I	J
14	D001	D002	D003	D004
15	P001	P008	P006	P110
16	P005
17	P009
18
19

G15=IFERROR(INDEX($E$2:$E$7,SMALL(IF($D$2:$D$7=G$14,ROW($E$2:$E$7)-1),ROWS($A$1:A1))),"")
copied down and across. This is an ARRAY formula...

...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. Press F2 on that cell and try again.

If i wish i can turn row 14 into column
like
D001 P001 P005 P009
D002 P008
D003 P006
D004 P110

How do i change the array?
Thank you so much for your time and answer!!
its brilliant!!