VLOOKUP with more than one possible response
 

I have a list of employees, Col A = Surname, Col B = Christian name, and Col
C = Employee number. On another sheet i have the same names but in a
different order (actually departments), to which i need to add the correct
employee number. VLOOKUP works but returns the first match only. When i
have two identical surnames, how do i then check the Christian names, to get
the correct employee number?
example:
A B C
Jones Jane 123
Jones Jeff 124

--
Big dog is always right - The fugitive

VLOOKUP with more than one possible response
 

If employee number is always a true number:

=SUMPRODUCT((A2:A10="Jones")*(B2:B10="Jane")*(C2:C 10))

If it might be text:
=INDEX(C1:C10,SUMPRODUCT((A2:A10="Jones")*(B2:B10= "Jane")*ROW(C2:C10)))

Note that ranges in SUMPRODUCT must be equal in size, and can't callout
entire column (A:A) unless using XL 2007. If you use the INDEX formula, make
sure it starts at row 1.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Andrew - Big Dog" wrote:

I have a list of employees, Col A = Surname, Col B = Christian name, and Col
C = Employee number. On another sheet i have the same names but in a
different order (actually departments), to which i need to add the correct
employee number. VLOOKUP works but returns the first match only. When i
have two identical surnames, how do i then check the Christian names, to get
the correct employee number?
example:
A B C
Jones Jane 123
Jones Jeff 124

--
Big dog is always right - The fugitive

VLOOKUP with more than one possible response
 

Hi Andrew

I would use a helper column, say column D, with a formula of
=A1&" "&B1
Copy down as required
With the Name to be looked up entered in G1 as Jones Jeff, then
=INDEX(C:C,MATCH(G1,D:D,0))
will return your result

--
Regards
Roger Govier

"Andrew - Big Dog" wrote in message
...
I have a list of employees, Col A = Surname, Col B = Christian name, and
Col
C = Employee number. On another sheet i have the same names but in a
different order (actually departments), to which i need to add the correct
employee number. VLOOKUP works but returns the first match only. When i
have two identical surnames, how do i then check the Christian names, to
get
the correct employee number?
example:
A B C
Jones Jane 123
Jones Jeff 124

--
Big dog is always right - The fugitive