vlookup for multiple entries

**krayziez** · 03-08-2008, 10:37 PM

is there a way i can list all entries that the vlookup looks up? example:

source:
1=r
2=b
2=c
3=u
3=e

if i do a vlookup to look up "2" it will have "b" and "c" but only "b" will show up since it's the first one. i need to be able to have it as "b ; c" as the output..

**mikerickson** · 03-08-2008, 11:32 PM

krayziez,

This UDF should help.
If you have 1;2;2;3;3 in A1:A5 and r;b;c;u;e in B1:B5

=ConcatIf(A1:A10,2,B1:B10,",") will return "b,c" (no quotes)

The arguments for ConcatIf are modled after SumIf, the compareRange is compared to the Criteria. The string is built from members of the stringsRange. Optional arguments give control of the delimter or adds a NOT to the criteria.

Please Login or Register  to view this content.

**krayziez** · 03-09-2008, 02:09 PM

doesn't seem to work.. it says #name?

**mikerickson** · 03-09-2008, 02:30 PM

The code should be put in a normal module. (not a sheet's code module or ThisWorkbook).
If that has been done, try entering it with the Insert Function, under UserDefined, rather than typing it.

Odd note: Excel's automatic capitalization of a UDF (concatif vs. ConcatIf) is determined by the capitalization entered by the user, the first time that a UDF is entered into a spreadsheet.

**krayziez** · 03-13-2008, 12:14 PM

OK. this line here is red and says Syntax error:

Please Login or Register  to view this content.

**mikerickson** · 03-13-2008, 09:05 PM

A thousand apologies, this version has the misplaced parenthesis in the right place.

Please Login or Register  to view this content.

**Steve R** · 02-06-2009, 03:12 PM

Originally Posted by krayziez

is there a way i can list all entries that the vlookup looks up? example:

source:
1=r
2=b
2=c
3=u
3=e

if i do a vlookup to look up "2" it will have "b" and "c" but only "b" will show up since it's the first one. i need to be able to have it as "b ; c" as the output..

You can create a list without using udf/VBA.

To lookup the value 2 you can use this formula:
=INDEX(B1:B8,SMALL(IF(A1:A8=D1,ROW(A1:A8),""),ROW()))

To remove any errors in the above formula, use this formula:
=IF(ISERROR(INDEX(B1:B8,SMALL(IF(A1:A8=D1,ROW(A1:A8),""),ROW()))),"",INDEX(B1:B8,SMALL(IF(A1:A8=D1,ROW(A1:A8),""),ROW())))

See attached workbook.

**HandsomeDanny** · 11-13-2014, 10:18 AM

Is there a way to list the returned values in columns, rather than in rows. Specifically in the file (lookup_multiple_values.xls that can be downloaded), lets say I want to return all values found for A but lets say that each has a value assigned to it. Can I return the label and it's value?

Using the example in the spreadsheet, here's how it would look:

A CAR 50000 Lookup value: A CAR
B BUS 70000 ROCKET
C AIRPLANE 2 SHIP
D HELICOPTER 1
A ROCKET 0
C BOAT 50
A SHIP 0
B MOTORBIKE

What I would like to achieve is the above except that it should look like this:

A CAR 50000 Lookup value: A CAR 50000 ROCKET 0 SHIP 0
B BUS 70000
C AIRPLANE 2
D HELICOPTER 1
A ROCKET 0
C BOAT 50
A SHIP 0
B MOTORBIKE

Any help would greatly be appreciated

**protonLeah** · 11-13-2014, 05:34 PM

HandsomeDanny,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.