|
How can I do a lookup and get multiple row results?
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 |
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!! |
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 |
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!! |
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) |
| All times are GMT +1. The time now is 11:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com