Question about a complicated lookup

[Jack.Matos]

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.

 

[T. Valko]

Try this:

Entered in Sheet1 cell D1:

=IF(COUNTIF(Sheet2!$A$1:$A$10,$A1),VLOOKUP($A1,Sheet2!$A$1:$D$10,COLUMNS($D1:E1),0),"")

Copy across to F1 then down as needed.

 

[Sheeloo]

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]

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]

You're welcome. Thanks for the feedback!

 

[Tatiana]

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]

Try this array formula** entered in U1:

=INDEX(Sheet2!EM$1:EM$10,MATCH(1,(Sheet2!$E$1:$E$10=$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.