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 have two Excel spreadsheets. In the first spreadsheet I have two columns. Column A has 240 Accounts entries and Column B has Values for each account entry. Once again Spreadsheet 2 has its data in the same form.

What I want to achieve:

Compare each column's entry of Spreadsheet 2 against Spreadsheet 1.

Say Spreadsheet 2 column has 4 entries like 111,222,333,444 and suppose I want to validate the value for entry 111. Then my formula should search for 111 in Spreadsheet 1 Column A, then it should pick the value for it, then compare it with the value of the Spreadsheet 2 Column B entry for 111, and give me the difference.

share|improve this question

use a VLOOKUP:


Put this in every row on sheet 1 where there is a row of data, in a blank column next to the data. It would look at the ID in that row, look for that ID in sheet 2, then return the value it finds.


I would also recommend you use tables, this way you can dynamically refer to the ranges, meaning less work in future to keep the function working:


This should be useful:

And finally:

Looking at your last line, you want to find the difference between the two values?

So you could do this:




Without knowing more about your data I can't be sure if the two values are the right way around.

share|improve this answer
Nice answer @ClockeWork. Did the questioner even return to SU to check his answer? If not what is the point in taking out one's time to give answers for such questioners? – Prasanna Jul 26 '14 at 5:33
Thanks @Prasanna. It's a shame if they didn't come back, but if the answer helps anyone then it's worth the time :) – CLockeWork Jul 28 '14 at 7:58

Use a difference of vlookups. Assuming your desired comparison value is in cell E2, you could use


See below:

enter image description here

share|improve this answer

You must log in to answer this question.

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