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!!

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 :-)
------------------------------*------------------------------*----------------


"Rashmi" wrote in message
...
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!!

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

"Ken Wright" wrote:

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 :-)
------------------------------Â*------------------------------Â*----------------


"Rashmi" wrote in message
...
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!!

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!

In article ,
Rashmi wrote:

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

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!!!

"Domenic" wrote:

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!

In article ,
Rashmi wrote:

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

Rashmi wrote:
I do want to get this into a single cell

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)

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 he

http://xcell05.free.fr/

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

Biff

"Rashmi" wrote in message
...
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

"Ken Wright" wrote:

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 :-)
------------------------------*------------------------------*----------------


"Rashmi" wrote in message
...
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!!

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. :)

In article ,
"Biff" wrote:

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 he

http://xcell05.free.fr/

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

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

"Domenic" wrote in message
...
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. :)

In article ,
"Biff" wrote:

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 he

http://xcell05.free.fr/

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

Biff

Thanks Biff. I'll give this a try.

"Biff" wrote:

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 he

http://xcell05.free.fr/

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

Biff

"Rashmi" wrote in message
...
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

"Ken Wright" wrote:

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 :-)
------------------------------Â*------------------------------Â*----------------


"Rashmi" wrote in message
...
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!!

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

"Biff" wrote:

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 he

http://xcell05.free.fr/

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

Biff

"Rashmi" wrote in message
...
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

"Ken Wright" wrote:

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 :-)
------------------------------Â*------------------------------Â*----------------


"Rashmi" wrote in message
...
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!!