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:

123 123 123 654 654 789 789 123 654 789 <colgroup><col width="64" style="width:48pt"> </colgroup><tbody> </tbody>	Erica Minda Tani Erica Jay Craig Lucy Mona Peter Josh <colgroup><col width="64" style="width:48pt"> </colgroup><tbody> <!--EndFragment--></tbody>

<tbody>
</tbody>

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

	A	B	C	D	E	F	G
1			123
2	123	Erica		Erica	Minda	Tani	Mona
3	123	Minda
4	123	Tani
5	654	Erica
6	654	Jay
7	789	Craig
8	789	Lucy
9	123	Mona
10	654	Peter
11	789	Josh

<tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula D2 {=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))),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[UrbanJungleFashion]

Thank you so very much!!!!

maybe something like...

excel 2016 (windows) 32 bit

	a	b	c	d	e	f	g
1			123
2	123	erica		erica	minda	tani	mona
3	123	minda
4	123	tani
5	654	erica
6	654	jay
7	789	craig
8	789	lucy
9	123	mona
10	654	peter
11	789	josh

<tbody>
</tbody>

sheet1​

array formulas cell formula d2 {=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))),"")} <tbody> </tbody> entered with ctrl+shift+enter. if entered correctly, excel will surround with curly braces {}. note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[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