# Cross Referencing Data between Excel Sheets, plus a conditional statement

I have an Excel workbook with 2 sheets.

1st Sheet: Old User Data 2nd Sheet: New User Data

I want to add a new column to the 2nd sheet that contains the old user ID from the 1st sheet. To get this, I want to compare the email address between both sheets.

I think the pseudo for what I want is as follows:

``````IF (NEW!E2 equal OLD!H2) THEN (NEW!B2 becomes OLD!A2)
``````

So far I have, in a new column on the 2nd sheet the following:

``````=IF(NEW!E2=OLD!H2,NEW!B2=OLD!A2)
``````

Seems to be the logic of the IF statement that is wrong.

-

An excel `IF` formula has the following construction:

``````IF( <condition> , <value if condition true> , <value if condition false> )
``````

Immediately your formula has two big problems:

• No false clause.
• The true clause is a conditional, not a value.

What you need is another column, with an if statement that says (in pseudo!):

``````IF (NEW!E2 equal OLD!H2) THEN (set this cell to OLD!A2)
ELSE (set this cell to NEW!B2)
``````

Or, in Excel:

``````IF( NEW!E2=OLD!H2 , OLD!A2 , NEW!B2 )
``````

Obviously, you then end up with two ID columns on your sheet. But, once you're done you should be able to copy/paste-special-values this extra column over the real one.

## Update

Depending on how your data is structured this might be useless anyway.

It could be better to replace the `if`'s condition with some form of lookup, so you get (in pseudo!):

``````IF( [NEW!E2] is in OLD list ) THEN (set this cell to ID of matching entry)
ELSE (set this cell to NEW!B2)
``````

Or, in Excel (line breaks so it fits!):

``````IF( VLOOKUP(NEW!E2,OLD!A1:G1000,1) = NEW!E2 ,
VLOOKUP(NEW!E2,OLD!A1:G1000,2) ,
NEW!B2)
``````

Obviously, I've made up `OLD!A1:G1000` so you'd have to provide your real table dimensions, and you would need to change the matching column number (the last value in `VLOOKUP`), and make sure the data in `OLD` is sorted by the email address, and refactor `OLD` so the email address is the first column (`VLOOKUP` requires ascending order and only looks in the first column for data - then returns the data from the same row in the requested column).

## Update2:

I think I mis-read your question slightly, because what you ask for is a column of just the matching old values - in which case you don't need a false clause because when there isn't a match you want "nothing". In which case you can just use `""` as a "do nothing" false clause to get blank cells.

-
thankyou, I will try this now. – Tisch Sep 5 '10 at 16:14
I was almost there, the data types where mismatching as well which was causing issues. But thankyou kindly for your help. – Tisch Sep 5 '10 at 16:23
thank you, I actually used "no match" prior to your update, but thankyou. VLOOKUP was the one I was looking at originally, I think this is what I need to learn now! I've managed to sort that part of the spreadsheet out now though. I think VLOOKUP would have saved a lot of time. c'est la vie. – Tisch Sep 5 '10 at 21:09
VLOOKUP is king. I should have learnt this straight away. – Tisch Sep 5 '10 at 21:20