How can I do a lookup and get multiple row results?

R

Rashmi

I have data where the lookup column has more than one row resulting. I want
to retrieve all matches and put the result in one cell. Can anyone help?
Thanks!!
 
K

Ken Wright

Example?

is the data numeric or text. If numeric do you mean you want to sum all the
values in one column where they meet the VLOOKUP criteria (If so then you
really want SUMIF and not VLOOKUP).

Give us some more details, and perhaps an example of your data plus the
desired output so we don't have to guess at what you want to do.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission :)
------------------------------­------------------------------­----------------
 
R

Rashmi

Hi Ken,
The data is string. I want to lookup on program name and return all the
procs in which the program is in. Here is an example of the data:

PgmName ProcName
VAC001 VM29DX
VAC001 VR68MX
VAC001 VZ14RX
VAC005 VJ15DX

If I do a lookup on VAC001, I want the function to return
VM29DX,VR68MX,VZ14RX. Example:

Pgm Procs
VAC001 VM29DX,VR68MX,VZ14RX

I don't know how many hits each progam name will find (between 1-16).

Thanks for any help you can provide!

Rashmi
 
D

Domenic

Assumptions:

A1:B5 contains your source data

First row contains your headers/labels


Formula:

E2, copied across:

=IF(COLUMNS($E1:E1)<=COUNTIF($A$2:$A$5,$D2),INDEX($B$2:$B$5,SMALL(IF($A$2
:$A$5=$D2,ROW($A$2:$A$5)-ROW($A$2)+1),COLUMNS($E1:E1))),"")

....where D2 contains the program name of interest. The formula needs to
be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Actually, if you
need to, you can enter your list of program names in Column D, starting
at D2, enter the formula in E2, copy across and down.

Hope this helps!
 
B

Biff

Hi!

This can be done easily if the values returned are returned to individual
cells.

See Domenic's reply.

If you absolutely need to have the returned values all in a single cell it's
not so easy and requires a special add-in that has a function to do this.

That add-in can be found here:

http://xcell05.free.fr/

Click the English button and look for the MOREFUNC add-in.

Biff
 
D

Domenic

Thanks Biff!

I didn't realize that the OP was looking for the result to be returned
in a single cell. I missed that completely. :)
 
B

Biff

There's a couple of "practices" that send shivers down my spine:

1. wanting multiple results in a single cell

2. calculations based on colors, fonts, formats

ugh!

Biff
 
R

Rashmi

Thanks Biff. I'll give this a try.

Biff said:
Hi!

This can be done easily if the values returned are returned to individual
cells.

See Domenic's reply.

If you absolutely need to have the returned values all in a single cell it's
not so easy and requires a special add-in that has a function to do this.

That add-in can be found here:

http://xcell05.free.fr/

Click the English button and look for the MOREFUNC add-in.

Biff
Click to expand...
 
R

Rashmi

Thank you so much! I do want to get this into a single cell, as well, but
this is useful for some other results I need. Thanks so much!!!
 
R

Rashmi

Biff,
I did the add-in but do you know which function in the MOREFUNC list will do
this and do you have an example? Thanks for any light you or anyone else can
shed on this.

Rashmi
 
?

.

Rashmi said:
I do want to get this into a single cell
Click to expand...

If you can afford some extra columns (you could hide them later), use
Dominic's solution. Then, assuming you want the single cell output in
C2, enter something along the lines of the following:

=TRIM(E2&" "&F2&" "&G2&" "&H2)
 
Top