Home |
Search |
Today's Posts |
#1
|
|||
|
|||
vlookup for same name, multiple instances...
I want to return the 2nd field for Jim in a single list:
Jim Dell Jeff HP Carl Compaq Jim Toshiba Carl Sony Jim Lenovo Jeff IBM I want to pull Jim's accounts to another worksheet: HP Toshiba Lenovo When I use vlookup, it just returns HP and stops. How do I tell it to return the next instance of Jim? |
#2
|
|||
|
|||
Chris_Hatch wrote:
I want to return the 2nd field for Jim in a single list: Jim Dell Jeff HP Carl Compaq Jim Toshiba Carl Sony Jim Lenovo Jeff IBM I want to pull Jim's accounts to another worksheet: HP Toshiba Lenovo When I use vlookup, it just returns HP and stops. How do I tell it to return the next instance of Jim? If the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your workbook you can array enter =VLookups("Jim",dataRange,2) Alan Beban |
#3
|
|||
|
|||
Alan,
I get a #NAME? error when I put in your formula. I noticed you used "vlookups" instead of "vlookup" and you removed the "false" qualifier. Is that part of the solution? When I do =vlookup(Jim,range,2,false) it returns Dell. I want to the next time I put a vlookup command to return Toshiba and then Lenovo respectively. The way I have it now just keeps returning HP. How do I tell it to go to the next Jim's column two value? "Alan Beban" wrote: Chris_Hatch wrote: I want to return the 2nd field for Jim in a single list: Jim Dell Jeff HP Carl Compaq Jim Toshiba Carl Sony Jim Lenovo Jeff IBM I want to pull Jim's accounts to another worksheet: HP Toshiba Lenovo When I use vlookup, it just returns HP and stops. How do I tell it to return the next instance of Jim? If the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your workbook you can array enter =VLookups("Jim",dataRange,2) Alan Beban |
#4
|
|||
|
|||
You have to download from the link Alan provided to use the Vlookups
Function--it's not standard in Excel. tj "Chris_Hatch" wrote: Alan, I get a #NAME? error when I put in your formula. I noticed you used "vlookups" instead of "vlookup" and you removed the "false" qualifier. Is that part of the solution? When I do =vlookup(Jim,range,2,false) it returns Dell. I want to the next time I put a vlookup command to return Toshiba and then Lenovo respectively. The way I have it now just keeps returning HP. How do I tell it to go to the next Jim's column two value? "Alan Beban" wrote: Chris_Hatch wrote: I want to return the 2nd field for Jim in a single list: Jim Dell Jeff HP Carl Compaq Jim Toshiba Carl Sony Jim Lenovo Jeff IBM I want to pull Jim's accounts to another worksheet: HP Toshiba Lenovo When I use vlookup, it just returns HP and stops. How do I tell it to return the next instance of Jim? If the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your workbook you can array enter =VLookups("Jim",dataRange,2) Alan Beban |
#5
|
|||
|
|||
You could try this formula using resident XL functions:
Names in Column A Companies in Column B Name to lookup in C1 Enter this array formula in D1: =INDEX($B$1:$B$30,SMALL(IF($A$1:$A$30=$C$1,ROW($A$ 1:$A$30)),ROW(A1))) Must be entered using <Ctrl <Shift <Enter Which will *automatically* enclose the formula in curly brackets, Which *cannot* be done manually! Drag down to copy as many rows as you anticipate you might need in order to return all the possible valid returns. When you see a #NUM! error, you know that all instances are returned. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Chris_Hatch" wrote in message ... Alan, I get a #NAME? error when I put in your formula. I noticed you used "vlookups" instead of "vlookup" and you removed the "false" qualifier. Is that part of the solution? When I do =vlookup(Jim,range,2,false) it returns Dell. I want to the next time I put a vlookup command to return Toshiba and then Lenovo respectively. The way I have it now just keeps returning HP. How do I tell it to go to the next Jim's column two value? "Alan Beban" wrote: Chris_Hatch wrote: I want to return the 2nd field for Jim in a single list: Jim Dell Jeff HP Carl Compaq Jim Toshiba Carl Sony Jim Lenovo Jeff IBM I want to pull Jim's accounts to another worksheet: HP Toshiba Lenovo When I use vlookup, it just returns HP and stops. How do I tell it to return the next instance of Jim? If the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your workbook you can array enter =VLookups("Jim",dataRange,2) Alan Beban |
#6
|
|||
|
|||
Hi,
Here are some options: 1. I do this only once: First Mark your area incl. Headlines. Menu: Data/Filter/AutoFilter Klick on the small Arrow and to select Tom 2. I do this regularly: Mark your area incl. the Headlines (include a few extra rows, for the future) Menu: Data/Pivottable and Chart reports Drop Names at the top, Computers as Row Item AND as Data Item. Select Tom 3. I Want a formula: Make a list on Sheet2 A1: 1 A2: 2 .... B1: =VLOOKUP(A1,Sheet1!A1:B100;2;0) B2: =... (copy of the above)... .... Ola |
#7
|
|||
|
|||
I downloaded the file, but the formula is still not available to me. I've
never done that before though...is there some special command I need to perform to "load" those formulas into XL? "tjtjjtjt" wrote: You have to download from the link Alan provided to use the Vlookups Function--it's not standard in Excel. tj "Chris_Hatch" wrote: Alan, I get a #NAME? error when I put in your formula. I noticed you used "vlookups" instead of "vlookup" and you removed the "false" qualifier. Is that part of the solution? When I do =vlookup(Jim,range,2,false) it returns Dell. I want to the next time I put a vlookup command to return Toshiba and then Lenovo respectively. The way I have it now just keeps returning HP. How do I tell it to go to the next Jim's column two value? "Alan Beban" wrote: Chris_Hatch wrote: I want to return the 2nd field for Jim in a single list: Jim Dell Jeff HP Carl Compaq Jim Toshiba Carl Sony Jim Lenovo Jeff IBM I want to pull Jim's accounts to another worksheet: HP Toshiba Lenovo When I use vlookup, it just returns HP and stops. How do I tell it to return the next instance of Jim? If the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your workbook you can array enter =VLookups("Jim",dataRange,2) Alan Beban |
#8
|
|||
|
|||
I've never used the <Ctrl <Shift <Enter before. Is that something I press
once, then enter, or enter the data, then press it? Either way I can't seem to get it to add the curly parentheticals... "RagDyer" wrote: You could try this formula using resident XL functions: Names in Column A Companies in Column B Name to lookup in C1 Enter this array formula in D1: =INDEX($B$1:$B$30,SMALL(IF($A$1:$A$30=$C$1,ROW($A$ 1:$A$30)),ROW(A1))) Must be entered using <Ctrl <Shift <Enter Which will *automatically* enclose the formula in curly brackets, Which *cannot* be done manually! Drag down to copy as many rows as you anticipate you might need in order to return all the possible valid returns. When you see a #NUM! error, you know that all instances are returned. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Chris_Hatch" wrote in message ... Alan, I get a #NAME? error when I put in your formula. I noticed you used "vlookups" instead of "vlookup" and you removed the "false" qualifier. Is that part of the solution? When I do =vlookup(Jim,range,2,false) it returns Dell. I want to the next time I put a vlookup command to return Toshiba and then Lenovo respectively. The way I have it now just keeps returning HP. How do I tell it to go to the next Jim's column two value? "Alan Beban" wrote: Chris_Hatch wrote: I want to return the 2nd field for Jim in a single list: Jim Dell Jeff HP Carl Compaq Jim Toshiba Carl Sony Jim Lenovo Jeff IBM I want to pull Jim's accounts to another worksheet: HP Toshiba Lenovo When I use vlookup, it just returns HP and stops. How do I tell it to return the next instance of Jim? If the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your workbook you can array enter =VLookups("Jim",dataRange,2) Alan Beban |
#9
|
|||
|
|||
You should save the file as an Excel Add-In. Then in your working
workbook in the VB Editor click on Tools|References and check the downloaded file. Then the functions in it will be available to that workbook pretty much as though they were built-in functions. Alan Beban Chris_Hatch wrote: I downloaded the file, but the formula is still not available to me. I've never done that before though...is there some special command I need to perform to "load" those formulas into XL? "tjtjjtjt" wrote: You have to download from the link Alan provided to use the Vlookups Function--it's not standard in Excel. tj "Chris_Hatch" wrote: Alan, I get a #NAME? error when I put in your formula. I noticed you used "vlookups" instead of "vlookup" and you removed the "false" qualifier. Is that part of the solution? When I do =vlookup(Jim,range,2,false) it returns Dell. I want to the next time I put a vlookup command to return Toshiba and then Lenovo respectively. The way I have it now just keeps returning HP. How do I tell it to go to the next Jim's column two value? "Alan Beban" wrote: Chris_Hatch wrote: I want to return the 2nd field for Jim in a single list: Jim Dell Jeff HP Carl Compaq Jim Toshiba Carl Sony Jim Lenovo Jeff IBM I want to pull Jim's accounts to another worksheet: HP Toshiba Lenovo When I use vlookup, it just returns HP and stops. How do I tell it to return the next instance of Jim? If the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your workbook you can array enter =VLookups("Jim",dataRange,2) Alan Beban |
#10
|
|||
|
|||
After you enter the formula in D1, click in the formula bar or hit <F2
THEN, hold down <Ctrl and <Shift, and then hit <Enter. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Chris_Hatch" wrote in message ... I've never used the <Ctrl <Shift <Enter before. Is that something I press once, then enter, or enter the data, then press it? Either way I can't seem to get it to add the curly parentheticals... "RagDyer" wrote: You could try this formula using resident XL functions: Names in Column A Companies in Column B Name to lookup in C1 Enter this array formula in D1: =INDEX($B$1:$B$30,SMALL(IF($A$1:$A$30=$C$1,ROW($A$ 1:$A$30)),ROW(A1))) Must be entered using <Ctrl <Shift <Enter Which will *automatically* enclose the formula in curly brackets, Which *cannot* be done manually! Drag down to copy as many rows as you anticipate you might need in order to return all the possible valid returns. When you see a #NUM! error, you know that all instances are returned. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Chris_Hatch" wrote in message ... Alan, I get a #NAME? error when I put in your formula. I noticed you used "vlookups" instead of "vlookup" and you removed the "false" qualifier. Is that part of the solution? When I do =vlookup(Jim,range,2,false) it returns Dell. I want to the next time I put a vlookup command to return Toshiba and then Lenovo respectively. The way I have it now just keeps returning HP. How do I tell it to go to the next Jim's column two value? "Alan Beban" wrote: Chris_Hatch wrote: I want to return the 2nd field for Jim in a single list: Jim Dell Jeff HP Carl Compaq Jim Toshiba Carl Sony Jim Lenovo Jeff IBM I want to pull Jim's accounts to another worksheet: HP Toshiba Lenovo When I use vlookup, it just returns HP and stops. How do I tell it to return the next instance of Jim? If the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your workbook you can array enter =VLookups("Jim",dataRange,2) Alan Beban |
#11
|
|||
|
|||
RD, You're a genius! Thanks!
BTW, is there any trick to downloading Alan's file? I can't seem to get it into my XL... "RagDyer" wrote: After you enter the formula in D1, click in the formula bar or hit <F2 THEN, hold down <Ctrl and <Shift, and then hit <Enter. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Chris_Hatch" wrote in message ... I've never used the <Ctrl <Shift <Enter before. Is that something I press once, then enter, or enter the data, then press it? Either way I can't seem to get it to add the curly parentheticals... "RagDyer" wrote: You could try this formula using resident XL functions: Names in Column A Companies in Column B Name to lookup in C1 Enter this array formula in D1: =INDEX($B$1:$B$30,SMALL(IF($A$1:$A$30=$C$1,ROW($A$ 1:$A$30)),ROW(A1))) Must be entered using <Ctrl <Shift <Enter Which will *automatically* enclose the formula in curly brackets, Which *cannot* be done manually! Drag down to copy as many rows as you anticipate you might need in order to return all the possible valid returns. When you see a #NUM! error, you know that all instances are returned. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Chris_Hatch" wrote in message ... Alan, I get a #NAME? error when I put in your formula. I noticed you used "vlookups" instead of "vlookup" and you removed the "false" qualifier. Is that part of the solution? When I do =vlookup(Jim,range,2,false) it returns Dell. I want to the next time I put a vlookup command to return Toshiba and then Lenovo respectively. The way I have it now just keeps returning HP. How do I tell it to go to the next Jim's column two value? "Alan Beban" wrote: Chris_Hatch wrote: I want to return the 2nd field for Jim in a single list: Jim Dell Jeff HP Carl Compaq Jim Toshiba Carl Sony Jim Lenovo Jeff IBM I want to pull Jim's accounts to another worksheet: HP Toshiba Lenovo When I use vlookup, it just returns HP and stops. How do I tell it to return the next instance of Jim? If the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your workbook you can array enter =VLookups("Jim",dataRange,2) Alan Beban |
#12
|
|||
|
|||
I'm trying to do the same thing, but needs the results in a separate
workbook. I could use a little help on the syntax, so that I get the right fields. I'm looking at using column A on sheet 2 for the value that I'm looking for, which is in column H of sheet 1, and I need to return columns F through O from sheet 1 onto sheet 2. I would appreciate any help that can be provided. Thanks!! "Chris_Hatch" wrote: RD, You're a genius! Thanks! BTW, is there any trick to downloading Alan's file? I can't seem to get it into my XL... "RagDyer" wrote: After you enter the formula in D1, click in the formula bar or hit <F2 THEN, hold down <Ctrl and <Shift, and then hit <Enter. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Chris_Hatch" wrote in message ... I've never used the <Ctrl <Shift <Enter before. Is that something I press once, then enter, or enter the data, then press it? Either way I can't seem to get it to add the curly parentheticals... "RagDyer" wrote: You could try this formula using resident XL functions: Names in Column A Companies in Column B Name to lookup in C1 Enter this array formula in D1: =INDEX($B$1:$B$30,SMALL(IF($A$1:$A$30=$C$1,ROW($A$ 1:$A$30)),ROW(A1))) Must be entered using <Ctrl <Shift <Enter Which will *automatically* enclose the formula in curly brackets, Which *cannot* be done manually! Drag down to copy as many rows as you anticipate you might need in order to return all the possible valid returns. When you see a #NUM! error, you know that all instances are returned. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Chris_Hatch" wrote in message ... Alan, I get a #NAME? error when I put in your formula. I noticed you used "vlookups" instead of "vlookup" and you removed the "false" qualifier. Is that part of the solution? When I do =vlookup(Jim,range,2,false) it returns Dell. I want to the next time I put a vlookup command to return Toshiba and then Lenovo respectively. The way I have it now just keeps returning HP. How do I tell it to go to the next Jim's column two value? "Alan Beban" wrote: Chris_Hatch wrote: I want to return the 2nd field for Jim in a single list: Jim Dell Jeff HP Carl Compaq Jim Toshiba Carl Sony Jim Lenovo Jeff IBM I want to pull Jim's accounts to another worksheet: HP Toshiba Lenovo When I use vlookup, it just returns HP and stops. How do I tell it to return the next instance of Jim? If the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your workbook you can array enter =VLookups("Jim",dataRange,2) Alan Beban |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup for same name, multiple instances...
I have downloaded the array functions...and see the functions in the VB
Editor. But the functions aren't working in the spreadsheet. Is there something futher I need to do for this to work?? Thanks! "Alan Beban" wrote: You should save the file as an Excel Add-In. Then in your working workbook in the VB Editor click on Tools|References and check the downloaded file. Then the functions in it will be available to that workbook pretty much as though they were built-in functions. Alan Beban Chris_Hatch wrote: I downloaded the file, but the formula is still not available to me. I've never done that before though...is there some special command I need to perform to "load" those formulas into XL? "tjtjjtjt" wrote: You have to download from the link Alan provided to use the Vlookups Function--it's not standard in Excel. tj "Chris_Hatch" wrote: Alan, I get a #NAME? error when I put in your formula. I noticed you used "vlookups" instead of "vlookup" and you removed the "false" qualifier. Is that part of the solution? When I do =vlookup(Jim,range,2,false) it returns Dell. I want to the next time I put a vlookup command to return Toshiba and then Lenovo respectively. The way I have it now just keeps returning HP. How do I tell it to go to the next Jim's column two value? "Alan Beban" wrote: Chris_Hatch wrote: I want to return the 2nd field for Jim in a single list: Jim Dell Jeff HP Carl Compaq Jim Toshiba Carl Sony Jim Lenovo Jeff IBM I want to pull Jim's accounts to another worksheet: HP Toshiba Lenovo When I use vlookup, it just returns HP and stops. How do I tell it to return the next instance of Jim? If the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your workbook you can array enter =VLookups("Jim",dataRange,2) Alan Beban |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup for same name, multiple instances...
liseladele wrote:
I have downloaded the array functions...and see the functions in the VB Editor. But the functions aren't working in the spreadsheet. Is there something futher I need to do for this to work?? Thanks! In the workbook of the spreadsheet in which you want the functions to work, Click on Tools, VBEditor, Tools, References, and check the reference to the file in which the functions reside. Post back if that doesn't solve the problem. Alan Beban |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup for same name, multiple instances...
I guess I'm unsure of which reference is the one to check...any help??
Thanks!! "Alan Beban" wrote: liseladele wrote: I have downloaded the array functions...and see the functions in the VB Editor. But the functions aren't working in the spreadsheet. Is there something futher I need to do for this to work?? Thanks! In the workbook of the spreadsheet in which you want the functions to work, Click on Tools, VBEditor, Tools, References, and check the reference to the file in which the functions reside. Post back if that doesn't solve the problem. Alan Beban |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup for same name, multiple instances...
hm....now it's saying...'must select 15 rows'...any ideas?? Thanks!
"Alan Beban" wrote: liseladele wrote: I have downloaded the array functions...and see the functions in the VB Editor. But the functions aren't working in the spreadsheet. Is there something futher I need to do for this to work?? Thanks! In the workbook of the spreadsheet in which you want the functions to work, Click on Tools, VBEditor, Tools, References, and check the reference to the file in which the functions reside. Post back if that doesn't solve the problem. Alan Beban |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup for same name, multiple instances...
Hi all,
I have the same problem. I am trying to lookup the value of one sheet, in another sheet. Example: Sheet1: Column A Andie Sam Mary John Carlos Sheet2: Column A Column B Andie Smith Claudia Mensi Michele Castro Andie Robertson Mary Johnson Andie Berg So I want to look up Andie in sheet2, and get the multiple results in sheet 1. Any ideas? Many thanks! "cursednomore" wrote: I'm trying to do the same thing, but needs the results in a separate workbook. I could use a little help on the syntax, so that I get the right fields. I'm looking at using column A on sheet 2 for the value that I'm looking for, which is in column H of sheet 1, and I need to return columns F through O from sheet 1 onto sheet 2. I would appreciate any help that can be provided. Thanks!! "Chris_Hatch" wrote: RD, You're a genius! Thanks! BTW, is there any trick to downloading Alan's file? I can't seem to get it into my XL... "RagDyer" wrote: After you enter the formula in D1, click in the formula bar or hit <F2 THEN, hold down <Ctrl and <Shift, and then hit <Enter. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Chris_Hatch" wrote in message ... I've never used the <Ctrl <Shift <Enter before. Is that something I press once, then enter, or enter the data, then press it? Either way I can't seem to get it to add the curly parentheticals... "RagDyer" wrote: You could try this formula using resident XL functions: Names in Column A Companies in Column B Name to lookup in C1 Enter this array formula in D1: =INDEX($B$1:$B$30,SMALL(IF($A$1:$A$30=$C$1,ROW($A$ 1:$A$30)),ROW(A1))) Must be entered using <Ctrl <Shift <Enter Which will *automatically* enclose the formula in curly brackets, Which *cannot* be done manually! Drag down to copy as many rows as you anticipate you might need in order to return all the possible valid returns. When you see a #NUM! error, you know that all instances are returned. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Chris_Hatch" wrote in message ... Alan, I get a #NAME? error when I put in your formula. I noticed you used "vlookups" instead of "vlookup" and you removed the "false" qualifier. Is that part of the solution? When I do =vlookup(Jim,range,2,false) it returns Dell. I want to the next time I put a vlookup command to return Toshiba and then Lenovo respectively. The way I have it now just keeps returning HP. How do I tell it to go to the next Jim's column two value? "Alan Beban" wrote: Chris_Hatch wrote: I want to return the 2nd field for Jim in a single list: Jim Dell Jeff HP Carl Compaq Jim Toshiba Carl Sony Jim Lenovo Jeff IBM I want to pull Jim's accounts to another worksheet: HP Toshiba Lenovo When I use vlookup, it just returns HP and stops. How do I tell it to return the next instance of Jim? If the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your workbook you can array enter =VLookups("Jim",dataRange,2) Alan Beban |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup for same name, multiple instances...
How do you want them to appear in Sheet1? Do you want to get Smith in
column B and Robertson in column C then Berg in column D etc? Pete On Feb 6, 10:15*am, Maria wrote: Hi all, I have the same problem. I am trying to lookup the value of one sheet, in another sheet. Example: Sheet1: Column A Andie Sam Mary John Carlos Sheet2: Column A * *Column B Andie * * * * *Smith Claudia * * * Mensi Michele * * * Castro Andie * * * * *Robertson Mary * * * * *Johnson Andie * * * * Berg So I want to look up Andie in sheet2, and get the multiple results in sheet 1. Any ideas? Many thanks! |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup for same name, multiple instances...
Hi Pete,
yes, exactly! "Pete_UK" wrote: How do you want them to appear in Sheet1? Do you want to get Smith in column B and Robertson in column C then Berg in column D etc? Pete On Feb 6, 10:15 am, Maria wrote: Hi all, I have the same problem. I am trying to lookup the value of one sheet, in another sheet. Example: Sheet1: Column A Andie Sam Mary John Carlos Sheet2: Column A Column B Andie Smith Claudia Mensi Michele Castro Andie Robertson Mary Johnson Andie Berg So I want to look up Andie in sheet2, and get the multiple results in sheet 1. Any ideas? Many thanks! |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup for same name, multiple instances...
I would suggest, then, that you insert a new column A in Sheet2 and
put this formula in the new A1: =IF(B1="","-",B1&"_"&COUNTIF(B$1:B1,B1)) Then copy this down as far as you need (or even further - it will show "-" in the cell if you have no data in column B, but this won't affect things). Then in B1 of Sheet1 you can use this formula: =IF(ISNA(MATCH($A1&"_"&COLUMN(A1),Sheet2!$A:$A,0)) ,"",INDEX(Sheet2!$C: $C,MATCH($A1&"_"&COLUMN(A1),Sheet2!$A:$A,0)) Then you can copy this across as far as you think you need (eg if there might be 10 people with Andie as a name, you will need to copy it that far across, but it doesn't matter if you copy it further). Then copy these cells down to cover your names. Hope this helps. Pete On Feb 6, 11:59*am, Maria wrote: Hi Pete, yes, exactly! "Pete_UK" wrote: How do you want them to appear in Sheet1? Do you want to get Smith in column B and Robertson in column C then Berg in column D etc? Pete On Feb 6, 10:15 am, Maria wrote: Hi all, I have the same problem. I am trying to lookup the value of one sheet, in another sheet. Example: Sheet1: Column A Andie Sam Mary John Carlos Sheet2: Column A * *Column B Andie * * * * *Smith Claudia * * * Mensi Michele * * * Castro Andie * * * * *Robertson Mary * * * * *Johnson Andie * * * * Berg So I want to look up Andie in sheet2, and get the multiple results in sheet 1. Any ideas? Many thanks!- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VlookUp with Multiple Criteria? | Excel Worksheet Functions | |||
vlookup with multiple lines of same value | Excel Worksheet Functions | |||
vlookup over multiple worksheets | Excel Worksheet Functions | |||
Can vlookup return multiple matches in a single cell? | Excel Discussion (Misc queries) | |||
Multiple Vlookup? | Excel Worksheet Functions |