is nested Vlookup possible with double values

[Marcia]

I'm fairly proficient with VLOOKUP but have never had to try a nested one to
look up 2 values at once so not sure if it's possible. My worksheets
display last name and first name in 2 separate columns on 2 different
worksheets. I need to compare the worksheets and return the value (like
VLOOKUP does) if the name matches for both the last and first name. Here's
an example of my 2 sheets to help with the illustration

Sheet 1
ANDRTREN Andrews Trent
AUSTJEFF Austin Jeffrey
AUSTRONA Austin Ronald

Sheet 2
DL - MM Alinger Erik
DL - MM Andrews Trent
DL - JH Austin Ronald

I'm really needing a function on Sheet 2 that looks for the last name that
matches on Sheet1 (Austin for example) and then compare the first name of
the same line to make sure it matches (Ronald for example). If I just match
last names, I'm not sure I've got an exact match on Sheet 1 because there
are 2 people with the last name Austin.

Here's my entry in cell D2 of Sheet 2 that I then copy down. It gives me
#VALUE!

=VLOOKUP(B2,Sheet1!B:B,VLOOKUP(C2,Sheet1!C:C,1,FALSE))

Is it even possible to do what I'm trying to accomplish? Please let me know
if any clarifications are needed. Thanks in advance,
Marcia

 

[Frank Kabel]

Hi
one way:
- on sheet 2 create a new helper column A which combines column B+C.
e.g. enter the formula =B1&C1 in A1 and copy down
- now you can use VLOOKUP as follows:
=VLOOKUP(B2&C2,Sheet1!A11000,2,0)

 

[Marcia]

Thanks Frank! That did let me compare the 2 names more easily than what I
was trying to do.

Marcia