returning multiple results from a vlookup onto single row

[ellison]

Hi,


is there a way to do a vlookup between 2 different lists where the "multiple" hits are returned - rather than only the first one?


(we were using the vlookup, but it only gives us the "first" reference that it looks up)


What we'd like is for the 1st instance to be put in Col B, the second instance in Col C, the 3rd in Col D etc


Just to add to the complications(!), we are trying to do this using a partial lookup eg =vlookup("*"&A1&"*",table array,col_index_sum,FALSE)


EG

List 1	Info from Lookup A	Info from Lookup B	Info from Lookup C
post	red	yellow	green
gate	white	black
List 2
posts	red
posted	yellow
post	green
med gate	white
large gate	black

<tbody>
</tbody>

 

[ClerkBot77]

Spreadsheet Formulas

Cell Formula A8 =IFERROR(INDEX(INDEX($D$2:$J$4,MATCH($A$7,$A$2:$A$4,0),0),AGGREGATE(15,6,(COLUMN($D$1:$J$1)-COLUMN($D$1)+1)/(INDEX($D$2:$J$4,MATCH($A$7,$A$2:$A$4,0),0)<>0),ROWS($A$14:A14))),"") <tbody> </tbody>

<tbody>
</tbody>

So when I took this formula. It works in this scale. If I try to expand it to my full sheet (date columns 90+ and employee rows 180+) I fail miserably. Currently attempting:

=IFERROR(INDEX(INDEX($D$2:$CO$198,MATCH($A$199,$A$2:$A$198,0),0),AGGREGATE(15,6,(COLUMN($D$1:$CO$1)-COLUMN($D$1)+1)/(INDEX($D$2:$CO$198,MATCH($A$199,$A$2:$A$198,0),0)<>0),ROWS($A$207:A207))),"")

which only returns the first instance of a non-zero

 

[ClerkBot77]

@ ClerkBot77

Looks like an ISTEXT would be appropriate...

	A	B	C	D	E	F	G	H	I	J	K	L
1	NAME	TITLE	HIRE DATE	1/1/2019	1/2/2019	1/3/2019	1/4/2019	1/5/2019	1/6/2019	1/7/2019		person b
2	PERSON A	PACKING	9/15/2015	A	LS14	0	0	LS16	0	0		X
3	PERSON B	PACKING	10/10/2018	0	X	LL61	LL11	0	0	0		LL61
4	PERSON C	PACKING	11/15/2017	A	0	0	0	LS16	0	0		LL11
5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

In L2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($D$2:$J$4,MATCH(L$1,$A$2:$A$4,0),SMALL(IF(ISTEXT(INDEX($D$2:$J$4,MATCH(L$1,$A$2:$A$4,0),0)),COLUMN($D$2:$J$4)-COLUMN($D$2)+1),ROWS(L$2:L2))),"")

This doesn't return any results.

 

[sandy666]

This doesn't return any results.

there was a any typo, I didn't analyse it because Excel corrected it for me

try

=IFERROR(INDEX($D$2:$J$4,MATCH(L$1,$A$2:$A$4,0),SMALL(IF(ISTEXT(INDEX($D$2:$J$4,MATCH(L$1,$A$2:$A$4,0),0)),COLUMN($D$2:$J$4)-COLUMN($D$2)+1),ROWS(L$2:L2))),"")

with CSE

	L​	M​	N​
1​	Person A	Person B	Person C
2​	A	X	A
3​	LS14	LL61	LS16
4​	LS16	LL11
5​

Add headers manually
Formula (in this case) in L2 Control+Shift+Enter drag to the right then down

 

[Aladin Akyurek]

This doesn't return any results.

Column L of the Excel readable visual shows the results that the suggested formula delivers. Did you apply control+shift+enter as requested?

Control+shift+enter: Press down the control and the shift keys at the same time while you hit the enter key. When done successfully, Excel itself puts a pair of { and } around the formula in recognition.

 

[ClerkBot77]

there was a any typo, I didn't analyse it because Excel corrected it for me

try

=IFERROR(INDEX($D$2:$J$4,MATCH(L$1,$A$2:$A$4,0),SMALL(IF(ISTEXT(INDEX($D$2:$J$4,MATCH(L$1,$A$2:$A$4,0),0)),COLUMN($D$2:$J$4)-COLUMN($D$2)+1),ROWS(L$2:L2))),"")

with CSE

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]L[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]M[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]N[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​	Person A	Person B	Person C
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​	A	X	A
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​	LS14	LL61	LS16
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​	LS16	LL11
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​

<tbody>
</tbody>

Add headers manually
Formula (in this case) in L2 Control+Shift+Enter drag to the right then down

Thank you, this is working out.

Is there a good way to also capture the date in a separate column?