# Error message when trying to retrieve a value from a range using VLOOKUP

I've got an excel spreadsheet where I would normally use a VLOOKUP.

In this case I need to find the ID of the record when comparing email addresses, so the email address is the unique id here.

For example on sheet 1

A             B              C            D
Person Id  |  Family Name  | First Name | Email
#N/A    |  Doe          | John       | jd@email.com

On Sheet 2

A             B              C            D
Person Id  |  Family Name  | First Name | Email
12345      |  Doe          | John       | jd@email.com

Basically on sheet 1 I've got 800 records, on sheet 2 450. I know the 450 are in Sheet 1, so I need to find the ids of those, and put them on sheet 1 where I've got lots more data for each person.

What I've tried so far is a VLOOKUP, but I keep getting an error. I'd like to do it with some sort of formula and not using any copy paste and remove duplicates.

Any ideas?

-

You're getting an error most likely because your lookup value is located to the right of the value you are trying to retrieve in your source (Sheet2, in your case).

Basically, your current formula should work if your source data sheet (Sheet2) is arranged this way:

A                 B              C            D
Email           |  Family Name  | First Name | Person ID
jd@email.com    |  Doe          | John       | XXXXXXX

where the Person ID column can placed in columns B, C or D ( as long as it's to the right of the Email column).

To avoid having to rearrange your data, instead of using VLOOKUP, I recommend using an INDEX/MATCH combo in column A of Sheet1, something along the lines of:

=INDEX(Sheet2!A:A,match(D1,Sheet2!D:D,0),1)

where
D1 is your lookup value (email address)
Sheet2!A:A contains Person IDs
Sheet2!D:D contains Emails

-
That's perfect! Thanks a lot! – Federico Giust Jun 1 '12 at 9:58
Glad to help. Welcome to SuperUser! – Ellesa Jun 1 '12 at 10:02
For the combo (much better!) solution, should not “Sheet1” and “Sheet2” all be transposed (and not “D1”s but, say, “D3”s)? Obviously the answer was well appreciated and ‘did the trick’ but it is being referred to from elsewhere. – pnuts Jul 13 '12 at 12:08
@pnuts I'm not sure what you mean. – Ellesa Jul 13 '12 at 14:05
Sheet2 is the one that has the ID numbers (and the columns have data labels, so D1 is probably already gainfully occupied). No big deal but could confuse someone referred to this as the right answer to another question. – pnuts Jul 13 '12 at 15:19