Return multiple lookup results in different cells

[kacker17]

Hello,

I'm trying to essentially do something similar to a vlookup, but instead of it just returning a single result, I want to see all results. I would like to have each result show in a different column.

Here's what I have in mind:

Basic Data Set

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]Category[/TD]
[TD="class: xl63, width: 64"]Item[/TD]
[/TR]
[TR]
[TD]Fruit[/TD]
[TD]Banana[/TD]
[/TR]
[TR]
[TD]Fruit[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]Fruit[/TD]
[TD]Peach[/TD]
[/TR]
[TR]
[TD]Fruit[/TD]
[TD]Raspberry[/TD]
[/TR]
[TR]
[TD]Fruit[/TD]
[TD]Grape[/TD]
[/TR]
[TR]
[TD]Fruit[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]Fruit[/TD]
[TD]Grapefruit[/TD]
[/TR]
[TR]
[TD]Veggie[/TD]
[TD]Celery[/TD]
[/TR]
[TR]
[TD]Veggie[/TD]
[TD]Tomato[/TD]
[/TR]
[TR]
[TD]Veggie[/TD]
[TD]Cauliflower[/TD]
[/TR]
[TR]
[TD]Veggie[/TD]
[TD]Potato[/TD]
[/TR]
[TR]
[TD]Veggie[/TD]
[TD]Brussel Sprouts[/TD]
[/TR]
[TR]
[TD]Veggie[/TD]
[TD]Brocoli[/TD]
[/TR]
[TR]
[TD]Drink[/TD]
[TD]Soda[/TD]
[/TR]
[TR]
[TD]Drink[/TD]
[TD]Water[/TD]
[/TR]
[TR]
[TD]Drink[/TD]
[TD]OJ[/TD]
[/TR]
[TR]
[TD]Drink[/TD]
[TD]Milk
[/TD]
[/TR]
</tbody>[/TABLE]

I would like it to display the values in this format:

[TABLE="width: 566"]
<colgroup><col span="3"><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD]Value 1[/TD]
[TD]Value 2[/TD]
[TD]Value 3[/TD]
[TD]Value 4[/TD]
[TD]Value 5[/TD]
[TD]Value 6[/TD]
[TD]Value 7[/TD]
[/TR]
[TR]
[TD]Fruit[/TD]
[TD]Banana[/TD]
[TD]Apple[/TD]
[TD]Peach[/TD]
[TD]Raspberry[/TD]
[TD]Grape[/TD]
[TD]Orange[/TD]
[TD]Grapefruit[/TD]
[/TR]
[TR]
[TD]Veggie[/TD]
[TD]Celery[/TD]
[TD]Tomato[/TD]
[TD]Cauliflower[/TD]
[TD]Potato[/TD]
[TD]Brussel Sprouts[/TD]
[TD]Brocoli[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Drink[/TD]
[TD]Soda[/TD]
[TD]Water[/TD]
[TD]OJ[/TD]
[TD]Milk[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have found a variety of solutions online incorporating everything from array formulas using Index and Small functions and a non array formula using Index and Aggregate. I CANNOT get any of these solutions to work for me. I have been at this for HOURS and am super frustrated.

If anyone has a solution it would REALLY help me out a ton.

Thanks,

Kristie

 

[Sam_D_Ben]

Something like this.

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K
1	DATA	CATEGORY
2	Fruit	Banana
3	Fruit	Apple
4	Fruit	Peach
5	Fruit	Raspberry		Fruit	Banana	Apple	Peach	Raspberry	Grape	Orange	Grapefruit
6	Fruit	Grape		Veggie	Celery	Tomato	Cauliflower	Potato	Brussel Sprouts	Brocoli
7	Fruit	Orange		Drink	Soda	Water	OJ	Milk
8	Fruit	Grapefruit
9	Veggie	Celery
10	Veggie	Tomato
11	Veggie	Cauliflower
12	Veggie	Potato
13	Veggie	Brussel Sprouts
14	Veggie	Brocoli
15	Drink	Soda
16	Drink	Water
17	Drink	OJ
18	Drink	Milk
Sheet1

Cell Formulas
Range	Formula
E5	{=IFERROR(INDEX($B$2:$B$18,SMALL(IF($D5=$A$2:$A$18,ROW($A$2:$A$18)- MIN(ROW($A$2:$A$18))+1,""),COLUMN()-4)), "")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

@ Sam(?)
Two comments:
1. MIN(ROW($A$2:$A$18)) can be written more simply as ROW($A$2)
2. Your formula is not robust against column Insertion/deletion. Try inserting or deleting any columns to the left of your formulas.

@ Kristie
Here is my version using AGGREGATE and Sam's layout.
The formula in E5 is copied across as far as you might ever need and down.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Category	Item
2	Fruit	Banana
3	Fruit	Apple
4	Fruit	Peach
5	Fruit	Raspberry		Fruit	Banana	Apple	Peach	Raspberry	Grape	Orange	Grapefruit
6	Fruit	Grape		Veggie	Celery	Tomato	Cauliflower	Potato	Brussel Sprouts	Brocoli
7	Fruit	Orange		Drink	Soda	Water	OJ	Milk
8	Fruit	Grapefruit
9	Veggie	Celery
10	Veggie	Tomato
11	Veggie	Cauliflower
12	Veggie	Potato
13	Veggie	Brussel Sprouts
14	Veggie	Brocoli
15	Drink	Soda
16	Drink	Water
17	Drink	OJ
18	Drink	Milk
19
Lookup All

 

[Sam_D_Ben]

@ Sam(?)
Two comments:
1. MIN(ROW($A$2:$A$18)) can be written more simply as ROW($A$2)
2. Your formula is not robust against column Insertion/deletion. Try inserting or deleting any columns to the left of your formulas.
Excel tables to the web >> Excel Jeanie HTML 4

Hi Peter,

Thank you for pointing out that, I applied it and found effective.

{=IFERROR(INDEX($B$2:$B$18,SMALL(IF($D5=$A$2:$A$18,ROW($A$2:$A$18)- (ROW($A$2))+1,""),COLUMN()-4)), "")}

 

[Peter_SSs]

I applied it and found effective.

{=IFERROR(INDEX($B$2:$B$18,SMALL(IF($D5=$A$2:$A$18,ROW($A$2:$A$18)- (ROW($A$2))+1,""),COLUMN()-4)), "")}

You applied point 1., but the point 2 issue is still present.

 

[kacker17]

You both are BRILLIANT!!!!! This ACTUALLY WORKED for me!!!!!!! Thank you so much for your very quick responses! Absolutely made my day!!!!!!

 

[Peter_SSs]

You both are BRILLIANT!!!!! This ACTUALLY WORKED for me!!!!!!! Thank you so much for your very quick responses! Absolutely made my day!!!!!!

You are very welcome.

I'm not sure which suggestion you used but if you used the formula from post 2 or 4, make sure that you note my point 2 in post 3. The robust form of that formula for E5 would be
{=IFERROR(INDEX($B$2:$B$18,SMALL(IF($D5=$A$2:$A$18,ROW($A$2:$A$18)- ROW($A$2)+1,""),COLUMNS($E5:E5))),"")}

If you have AGGREGATE though I would use that simply because you then don't have to remember to do the Ctrl+Shift+Enter any time you edit the formula.

 

[Sam_D_Ben]

The robust form of that formula for E5 would be
{=IFERROR(INDEX($B$2:$B$18,SMALL(IF($D5=$A$2:$A$18,ROW($A$2:$A$18)- ROW($A$2)+1,""),COLUMNS($E5:E5))),"")}

I got you. Thanks Peter.

@ Kacker17, Thanks