How To Return Multiple Match Values in Excel Using Hlookup and INDEX Match

[UrbanJungleFashion]

Hey everyone. I am trying to figure out how to write an Hlookup up formula that shows multiple results. I want my Hlookup to return multiple value horizontally instead of vertically. Please see below as an example:

Criteria:

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]123[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[/TR]
[TR]
[TD="align: right"]654[/TD]
[/TR]
[TR]
[TD="align: right"]654[/TD]
[/TR]
[TR]
[TD="align: right"]789[/TD]
[/TR]
[TR]
[TD="align: right"]789[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[/TR]
[TR]
[TD="align: right"]654[/TD]
[/TR]
[TR]
[TD="align: right"]789[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Erica[/TD]
[/TR]
[TR]
[TD]Minda[/TD]
[/TR]
[TR]
[TD]Tani[/TD]
[/TR]
[TR]
[TD]Erica[/TD]
[/TR]
[TR]
[TD]Jay[/TD]
[/TR]
[TR]
[TD]Craig[/TD]
[/TR]
[TR]
[TD]Lucy[/TD]
[/TR]
[TR]
[TD]Mona[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[/TR]
[TR]
[TD]Josh[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Desired result:

In Cell G5 I would input 123 and horizontally the names Erica, Minda, Tani, and Mona would populate.

Please let me know if that made sense.

 

[Weazel]

maybe something like...

Excel 2016 (Windows) 32 bit[TABLE="class: grid, width: 400"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]123[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]123[/TD]
[TD]Erica[/TD]
[TD="align: right"][/TD]
[TD]Erica[/TD]
[TD]Minda[/TD]
[TD]Tani[/TD]
[TD]Mona[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]123[/TD]
[TD]Minda[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]123[/TD]
[TD]Tani[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]654[/TD]
[TD]Erica[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]654[/TD]
[TD]Jay[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]789[/TD]
[TD]Craig[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]789[/TD]
[TD]Lucy[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]123[/TD]
[TD]Mona[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"]654[/TD]
[TD]Peter[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"]789[/TD]
[TD]Josh[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]{=IFERROR(INDEX($B$2:$B$11,SMALL(IF($A$2:$A$11=$C$1,ROW($A$2:$A$11)-ROW($A$2)+1),COLUMNS($D2:D2))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[UrbanJungleFashion]

Thank you so very much!!!!

maybe something like...

excel 2016 (windows) 32 bit[table="class: Grid, width: 400"]
<tbody>[tr="bgcolor: #dae7f5"]
[td][/td]
[td]a[/td]
[td]b[/td]
[td]c[/td]
[td]d[/td]
[td]e[/td]
[td]f[/td]
[td]g[/td]
[/tr]
[tr]
[td="align: Center"]1[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"]123[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[/tr]
[tr]
[td="align: Center"]2[/td]
[td="align: Right"]123[/td]
[td]erica[/td]
[td="align: Right"][/td]
[td]erica[/td]
[td]minda[/td]
[td]tani[/td]
[td]mona[/td]
[/tr]
[tr]
[td="align: Center"]3[/td]
[td="align: Right"]123[/td]
[td]minda[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[/tr]
[tr]
[td="align: Center"]4[/td]
[td="align: Right"]123[/td]
[td]tani[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[/tr]
[tr]
[td="align: Center"]5[/td]
[td="align: Right"]654[/td]
[td]erica[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[/tr]
[tr]
[td="align: Center"]6[/td]
[td="align: Right"]654[/td]
[td]jay[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[/tr]
[tr]
[td="align: Center"]7[/td]
[td="align: Right"]789[/td]
[td]craig[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[/tr]
[tr]
[td="align: Center"]8[/td]
[td="align: Right"]789[/td]
[td]lucy[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[/tr]
[tr]
[td="align: Center"]9[/td]
[td="align: Right"]123[/td]
[td]mona[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[/tr]
[tr]
[td="align: Center"]10[/td]
[td="align: Right"]654[/td]
[td]peter[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[/tr]
[tr]
[td="align: Center"]11[/td]
[td="align: Right"]789[/td]
[td]josh[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[/tr]
</tbody>[/table]

sheet1​

[table="width: 85%"]
<tbody>[tr]
[td]array formulas[table="width: 100%"]
<tbody>[tr="bgcolor: #dae7f5"]
[th]cell[/th]
[th="align: Left"]formula[/th]
[/tr]
[tr]
[th="bgcolor: #dae7f5"]d2[/th]
[td="align: Left"]{=iferror(index($b$2:$b$11,small(if($a$2:$a$11=$c$1,row($a$2:$a$11)-row($a$2)+1),columns($d2:d2))),"")}[/td]
[/tr]
</tbody>[/table]
entered with ctrl+shift+enter. if entered correctly, excel will surround with curly braces {}.
note: Do not try and enter the {} manually yourself[/td]
[/tr]
</tbody>[/table]

 

[RichardDunne]

Hi, I'm trying to do the exact same thing as above, but getting a #VALUE! error, a value in the formula is of the wrong data type.
I want to match values column N (N1:N222) with values in range A1:L234 and put values from column M into row 1 columns O,P,Q etc. My formula is

=INDEX(M1:M234,SMALL(IF(A1:L234=N1,ROW(A1:L234)-ROW(A1)+1),COLUMNS(O1:O1)),"")

The only part of the formula I don't understand is

-ROW(A1)+1)

I would appreciate any help and explanation, thanks.

 

[toniojst]

Hello
I also have a very similar case I would like to return to me all the whole lines where the ID is "Da". and if it's still possible to return it to a new page "OPOMNIK" sheet.


to explain the example:
I would like to make a list of services provided. In column C, I have written the dates of the performed sevisov's work, and I would like to point out that after the last one year I have to make a service. I'm checking this in column A for the answer "Da" or "Ne". but now I only need to print out all the lines that have in the ID column -> "Da"


greetings