vlookup

[billQ]

Hello,
I have 3 columns containing Last name, first name, and employee number. I
am trying to use vlookup to find the employee number based on the last name
and first name. I can use the vlookup without problem if I just use the
last name, In the event there are family members with the same last name I
need to find the emp number based on the first name. Is there a way to And
2 criteria into one vlookup?
thanks
bill

 

[Peo Sjoblom]

You can use a double lookup using index and match

Last names in A3:A200, first in B3:B300 and employee number in C3:C200


=INDEX($C$3:$C$200,MATCH(1,($A$3:$A$200=E2)*($B$3:$B$200=F2),0))

entered with ctrl + shift & enter

where E2 holds the last name lookup and F2 the first name lookup, that way
you don't have to edit the formula when you switch names, just change E2 and
F2

 

[billQ]

Hi Peo,
If I set this formula up on the page with the data it works like a champ. I
have the formula on a Sheet named Macro Stuff. The formula is as follows.
MinData is the named range for the employee numbers. LastName and FirstName
are also named ranges.

=INDEX(MinData,MATCH(1,(Database!A:A=LastName)*(Database!B:B=FirstName),0))
when the function is placed on the macrostuff page I get a #NUM! error. Any
ideas?
thanks
bill

 

[billQ]

Hello again
I tried
=Index(EmpNumRange, Match(LastName&FirstName,
LastNameRange&FirstNameRange,0))
I entered it with ctrl+shift+enter and it works like a champ
thanks
bill