Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I transform a word response to a numeric response? | Excel Discussion (Misc queries) | |||
How do I set up a daily call out response response register? | Excel Worksheet Functions | |||
VLOOKUP won't generate correct response for "inbetween" values | Excel Discussion (Misc queries) | |||
value response | Excel Discussion (Misc queries) | |||
can I get some help here? No response yet :) | Excel Worksheet Functions |