Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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       |

On Sheet 2

    A             B              C            D
Person Id  |  Family Name  | First Name | Email
12345      |  Doe          | John       |

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?

share|improve this question
up vote 4 down vote accepted

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    |  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:


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

share|improve this answer
That's perfect! Thanks a lot! – Federico Giust Jun 1 '12 at 9:58
Glad to help. Welcome to SuperUser! – Kaze 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. – Kaze 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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .