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

Question

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       | [e-mail]

On Sheet 2

    A             B              C            D
Person Id  |  Family Name  | First Name | Email
12345      |  Doe          | John       | [e-mail]

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?

Answer 1

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
[e-mail]    |  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