Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question about a complicated lookup
I have a list of names with duplicates in column A of sheet 1 and related
info in columns B & C . I have a second list in column A of sheet 2 containing some of those names and additional related info in subsequent columns that is different from the info on the 1st sheet. What I'm trying to accomplish is taking each value from the 1st sheet, compare it to the list on the 2nd sheet and once a match is found, copy the additional info from columns B,C & D on sheet 2 to columns D, E, & F on sheet 1, essentially merging the 2 sheets. Using Excel 2007 and have tried Vlookup with a nested match and index with a nested match but formula fails (#N/A for table array). Can someone point me in the right direction or point out what I'm failling to see. Thanks in advance for your assistance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question about a complicated lookup
Try this:
Entered in Sheet1 cell D1: =IF(COUNTIF(Sheet2!$A$1:$A$10,$A1),VLOOKUP($A1,She et2!$A$1:$D$10,COLUMNS($D1:E1),0),"") Copy across to F1 then down as needed. -- Biff Microsoft Excel MVP "Jack.Matos" wrote in message ... I have a list of names with duplicates in column A of sheet 1 and related info in columns B & C . I have a second list in column A of sheet 2 containing some of those names and additional related info in subsequent columns that is different from the info on the 1st sheet. What I'm trying to accomplish is taking each value from the 1st sheet, compare it to the list on the 2nd sheet and once a match is found, copy the additional info from columns B,C & D on sheet 2 to columns D, E, & F on sheet 1, essentially merging the 2 sheets. Using Excel 2007 and have tried Vlookup with a nested match and index with a nested match but formula fails (#N/A for table array). Can someone point me in the right direction or point out what I'm failling to see. Thanks in advance for your assistance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question about a complicated lookup
Enter this in D1 of Sheet 1
=IFERROR(Vlookup(Sheet2!$A:$D,Column()-2,False),"") and copy right to E and F columns and then down to the last row in your dataset "Jack.Matos" wrote: I have a list of names with duplicates in column A of sheet 1 and related info in columns B & C . I have a second list in column A of sheet 2 containing some of those names and additional related info in subsequent columns that is different from the info on the 1st sheet. What I'm trying to accomplish is taking each value from the 1st sheet, compare it to the list on the 2nd sheet and once a match is found, copy the additional info from columns B,C & D on sheet 2 to columns D, E, & F on sheet 1, essentially merging the 2 sheets. Using Excel 2007 and have tried Vlookup with a nested match and index with a nested match but formula fails (#N/A for table array). Can someone point me in the right direction or point out what I'm failling to see. Thanks in advance for your assistance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question about a complicated lookup
Biff,
I did as you recommended, and was able to see where I went wrong in my logic. I further expanded the array to include some additional cells and it works great. Thank you. Jack "T. Valko" wrote: Try this: Entered in Sheet1 cell D1: =IF(COUNTIF(Sheet2!$A$1:$A$10,$A1),VLOOKUP($A1,She et2!$A$1:$D$10,COLUMNS($D1:E1),0),"") Copy across to F1 then down as needed. -- Biff Microsoft Excel MVP "Jack.Matos" wrote in message ... I have a list of names with duplicates in column A of sheet 1 and related info in columns B & C . I have a second list in column A of sheet 2 containing some of those names and additional related info in subsequent columns that is different from the info on the 1st sheet. What I'm trying to accomplish is taking each value from the 1st sheet, compare it to the list on the 2nd sheet and once a match is found, copy the additional info from columns B,C & D on sheet 2 to columns D, E, & F on sheet 1, essentially merging the 2 sheets. Using Excel 2007 and have tried Vlookup with a nested match and index with a nested match but formula fails (#N/A for table array). Can someone point me in the right direction or point out what I'm failling to see. Thanks in advance for your assistance. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question about a complicated lookup
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Jack.Matos" wrote in message ... Biff, I did as you recommended, and was able to see where I went wrong in my logic. I further expanded the array to include some additional cells and it works great. Thank you. Jack "T. Valko" wrote: Try this: Entered in Sheet1 cell D1: =IF(COUNTIF(Sheet2!$A$1:$A$10,$A1),VLOOKUP($A1,She et2!$A$1:$D$10,COLUMNS($D1:E1),0),"") Copy across to F1 then down as needed. -- Biff Microsoft Excel MVP "Jack.Matos" wrote in message ... I have a list of names with duplicates in column A of sheet 1 and related info in columns B & C . I have a second list in column A of sheet 2 containing some of those names and additional related info in subsequent columns that is different from the info on the 1st sheet. What I'm trying to accomplish is taking each value from the 1st sheet, compare it to the list on the 2nd sheet and once a match is found, copy the additional info from columns B,C & D on sheet 2 to columns D, E, & F on sheet 1, essentially merging the 2 sheets. Using Excel 2007 and have tried Vlookup with a nested match and index with a nested match but formula fails (#N/A for table array). Can someone point me in the right direction or point out what I'm failling to see. Thanks in advance for your assistance. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question about a complicated lookup
Hello,
I need to do almost the same except that I need match two columns, let's say D and E, from sheet 1 to two columns E and H from sheet 2. If they match, copy information from columns EM and EN from sheet 2 to columns U and V on sheet 1. Is it possible to do? Thank you! "T. Valko" wrote: Try this: Entered in Sheet1 cell D1: =IF(COUNTIF(Sheet2!$A$1:$A$10,$A1),VLOOKUP($A1,She et2!$A$1:$D$10,COLUMNS($D1:E1),0),"") Copy across to F1 then down as needed. -- Biff Microsoft Excel MVP "Jack.Matos" wrote in message ... I have a list of names with duplicates in column A of sheet 1 and related info in columns B & C . I have a second list in column A of sheet 2 containing some of those names and additional related info in subsequent columns that is different from the info on the 1st sheet. What I'm trying to accomplish is taking each value from the 1st sheet, compare it to the list on the 2nd sheet and once a match is found, copy the additional info from columns B,C & D on sheet 2 to columns D, E, & F on sheet 1, essentially merging the 2 sheets. Using Excel 2007 and have tried Vlookup with a nested match and index with a nested match but formula fails (#N/A for table array). Can someone point me in the right direction or point out what I'm failling to see. Thanks in advance for your assistance. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question about a complicated lookup
Try this array formula** entered in U1:
=INDEX(Sheet2!EM$1:EM$10,MATCH(1,(Sheet2!$E$1:$E$1 0=$D1)*(Sheet2!$H$1:$H$10=$E1),0)) Copy across to V1 then down if needed. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Tatiana" wrote in message ... Hello, I need to do almost the same except that I need match two columns, let's say D and E, from sheet 1 to two columns E and H from sheet 2. If they match, copy information from columns EM and EN from sheet 2 to columns U and V on sheet 1. Is it possible to do? Thank you! "T. Valko" wrote: Try this: Entered in Sheet1 cell D1: =IF(COUNTIF(Sheet2!$A$1:$A$10,$A1),VLOOKUP($A1,She et2!$A$1:$D$10,COLUMNS($D1:E1),0),"") Copy across to F1 then down as needed. -- Biff Microsoft Excel MVP "Jack.Matos" wrote in message ... I have a list of names with duplicates in column A of sheet 1 and related info in columns B & C . I have a second list in column A of sheet 2 containing some of those names and additional related info in subsequent columns that is different from the info on the 1st sheet. What I'm trying to accomplish is taking each value from the 1st sheet, compare it to the list on the 2nd sheet and once a match is found, copy the additional info from columns B,C & D on sheet 2 to columns D, E, & F on sheet 1, essentially merging the 2 sheets. Using Excel 2007 and have tried Vlookup with a nested match and index with a nested match but formula fails (#N/A for table array). Can someone point me in the right direction or point out what I'm failling to see. Thanks in advance for your assistance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complicated Lookup | Excel Worksheet Functions | |||
Complicated lookup | Excel Worksheet Functions | |||
Lookup Help Complicated | Excel Discussion (Misc queries) | |||
Complicated Lookup Function | Excel Worksheet Functions | |||
Complicated value lookup | Excel Discussion (Misc queries) |