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.

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.

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.

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.

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.

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.

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.