Vlookup but with multiple results

[ORLANDO VAZQUEZ]

Thank you for your support.

Can someone please help me with the following array? type of question.

What formula can I use to return a list of results when there is more than
one result?
If I use VLOOKup it returns only the first instance of the result.


For example: Looking up cell a1 value of 22 should result in the following
list:
Dog
Biscuit
Steak

A B C D

22 34 Apple
33 34 Fish
16 22 Dog
91 1 Orange
15 3 Tangerine
14 22 Biscuit
21 1 Tea
34 5 Salmon
17 22 Steak
7 Herring
8 Cod
1 Orange
1 Castle

 

[Pete_UK]

You can set up another column to give you a unique reference. For
example, put this formula in E1:

=IF(C1="","",C1&"_"&COUNTIF(C$1:C1,C1))

then copy this down to cover the values in column C.

Then, with 22 in A1, you can put this formula in B1:

=IF(ISNA(MATCH(A$1&"_"&ROW(A1),E:E,0)),"",INDEX(D,MATCH(A$1&"_"&ROW
(A1),E:E,0)))

Then copy this down as far as you think you might need it (i.e. to get
all the duplicates).

Hope this helps.

Pete

 

[Mike H]

Hi,

Try this. Drag down to find multiple results. It produce NUM errors when it
doesn't find a resuly and you can wrap the whole thing in a n error trap to
elminate this.

=INDEX($D$1:$D$10,SMALL(IF(($C$1:$C$10=$A$1),ROW($C$1:$C$10)),ROW()))

'This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array


Mike

 

[ORLANDO VAZQUEZ]

This is very good. Thank you.

 

[ORLANDO VAZQUEZ]

This worked fine. Thank you !

 

[T. Valko]

Try this array formula** entered in cell E1:

=IF(ROWS(E$1:E1)<=COUNTIF(B$1:B$20,A$1),INDEX(C$1:C$20,SMALL(IF(B$1:B$20=A$1,ROW(C$1:C$20)),ROWS(E$1:E1))-MIN(ROW(C$1:C$20))+1),"")

Copy down until you get blanks meaning all the relative data has been
extracted.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[ORLANDO VAZQUEZ]

For some reason this returns a circular reference....

 

[ORLANDO VAZQUEZ]

This worked great. Thank you!

Hi,

Try this. Drag down to find multiple results. It produce NUM errors when it
doesn't find a resuly and you can wrap the whole thing in a n error trap to
elminate this.

=INDEX($D$1:$D$10,SMALL(IF(($C$1:$C$10=$A$1),ROW($C$1:$C$10)),ROW()))

'This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array


Mike

 

[ORLANDO VAZQUEZ]

Mike,

Thank you. This works good.

Hi,

Try this. Drag down to find multiple results. It produce NUM errors when it
doesn't find a resuly and you can wrap the whole thing in a n error trap to
elminate this.

=INDEX($D$1:$D$10,SMALL(IF(($C$1:$C$10=$A$1),ROW($C$1:$C$10)),ROW()))

'This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array


Mike

 

[ORLANDO VAZQUEZ]

This works very good. Thank you.

You can set up another column to give you a unique reference. For
example, put this formula in E1:

=IF(C1="","",C1&"_"&COUNTIF(C$1:C1,C1))

then copy this down to cover the values in column C.

Then, with 22 in A1, you can put this formula in B1:

=IF(ISNA(MATCH(A$1&"_"&ROW(A1),E:E,0)),"",INDEX(D,MATCH(A$1&"_"&ROW
(A1),E:E,0)))

Then copy this down as far as you think you might need it (i.e. to get
all the duplicates).

Hope this helps.

Pete

 

[T. Valko]

For some reason this returns a circular reference....

Where did you enter the formula? You must have entered it within one of the
referenced ranges. If your data really is where you said it was and you
enter the formula in E1 as I suggested there is no way you'll get a circular
reference. You can enter the formula anywhere *except* within the range
B1:C20.

While the other suggestions will work, this version is the most robust.

 

[ORLANDO VAZQUEZ]

I moved my data to correspond to the formula and it works great. Thank you!

 

[ORLANDO VAZQUEZ]

Mike,

I want to transpose the results so that all appear in row 1 rather than
stacked in column E. Can you help me ? I tried but cannot figure it out.
The reason is that I want each corresponding set of results to appear on the
line it corresponds to.

 

[T. Valko]

Thanks for feeding back!

You don't want to move data to accommodate a formula, you want to be able to
write the formula to accommodate the data.

That's why it's good idea to tell us where your data *really is located* and
where you want the results to appear. Very few people do this, though!!!

 

[ORLANDO VAZQUEZ]

Pete,
Can this be modified so the results appear in row 1 horizontally left to
right rather than in column e vertically ? And can I then copy that formula
down to apply to next row?

 

[T. Valko]

Try this array formula entered in E1:

=IF(COLUMNS($E1:E1)<=COUNTIF($B$1:$B$20,$A1),INDEX($C$1:$C$20,SMALL(IF($B$1:$B$20=$A1,ROW(C$1:C$20)),COLUMNS($E1:E1))-MIN(ROW(C$1:C$20))+1),"")

Copy down as needed then copy across until you get a *full column* of blanks
meaning all data has been extracted.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[ORLANDO VAZQUEZ]

Can this formula be modified so that the results appear in rows rather than
the one column?
Reason is I want to be able to copy the formula down to each row to apply to
each row and results sprawling out to right.

 

[ORLANDO VAZQUEZ]

Fantastic !

Try this array formula entered in E1:

=IF(COLUMNS($E1:E1)<=COUNTIF($B$1:$B$20,$A1),INDEX($C$1:$C$20,SMALL(IF($B$1:$B$20=$A1,ROW(C$1:C$20)),COLUMNS($E1:E1))-MIN(ROW(C$1:C$20))+1),"")

Copy down as needed then copy across until you get a *full column* of blanks
meaning all data has been extracted.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[T. Valko]

Yes, see my reply in the other branch of this thread.

 

[Pete_UK]

You're welcome - I see you've also had other help.

Pete