vlookup / index / match?

[James]

Hi,

I am having a problem setting up a formula that uses data from one
spreadsheet and returns a value in another.

Here are my spreadsheets:

Spreadsheet 1:
Col. A Col. B Col. C
1 Surname Firstname Classification
2 Bloggs Joe Management
3 Jones Fred Engineer
and so on

Spreadsheet 2:
Col. B Col. C Col. I
1 Surname Firstname Classification
2 Bloggs Joe
3 Jones Fred

In I2 (spreadsheet 2) I have the formula:
=vlookup(B1,'Spreadsheet2'!$A$2:$B$200,3) to get Management

This formula works fine when there is only one person with each surname.
When there are two people with the same surname, i.e. Joe Bloggs and John
Bloggs I don't get the correct result.

Can anyone help me?

Thanks

James

 

[reitanospa1]

There's no way for Excel to recognize the difference if you are only
searching for last name. One solution would be to concatenate (&)
first and last and search on that, but that still runs into the "we
have 5 people named John Smith" problem. You really need a unique
identifier like a Social or Employee ID.

 

[Teethless mama]

=INDEX(Sheet1!$C$2:$C$100,INDEX(MATCH(B2&C2,Sheet1!$A$2:$A$100&Sheet1!$B$2:$B$100,0),0))

 

[CLR]

To begin with, your formula would probably work better is modified as follows:

=vlookup(B1,'Spreadsheet2'!$A$2:$C$200,3,FALSE)

This would give the Lookup table sufficient range to find the 3rd,
offset......then the FALSE option would eliminate errors through mispelling

As for your surname problem....this can be worked around by doing a
CONCATENATION of the surnames and firstnames in a column to the left of your
lookup table.....then look up, BloggsJoe, or JonesFred to find unique records.

hth
Vaya con Dios,
Chuck, CABGx3

 

[James]

Thank you all for your help, it now works
--
J

To begin with, your formula would probably work better is modified as follows:

=vlookup(B1,'Spreadsheet2'!$A$2:$C$200,3,FALSE)

This would give the Lookup table sufficient range to find the 3rd,
offset......then the FALSE option would eliminate errors through mispelling

As for your surname problem....this can be worked around by doing a
CONCATENATION of the surnames and firstnames in a column to the left of your
lookup table.....then look up, BloggsJoe, or JonesFred to find unique records.

hth
Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Glad you got it going James...........thanks for the feedback.

Vaya con Dios,
Chuck, CABGx3