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:

=VLOOKUP($A2,Sheet2!$A2:$B$240,2,FALSE)

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.

=VLOOKUP(AdjacentCellWithID,TargetTable,NumberOfColumnsAcrossFromLeft,FALSE)


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:

=VLOOKUP([@[ID]],[ValuesTable],2,FALSE)


This should be useful: http://chandoo.org/wp/2012/03/30/comprehensive-guide-excel-vlookup/


And finally:

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

So you could do this:

=[@[Value]-VLOOKUP([@[ID]],[ValuesTable],2,FALSE)

or

=$B2-VLOOKUP($A2,Sheet2!$A2:$B$240,2,FALSE)

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

=VLOOKUP(E2,Sheet1!A:B,2,0)-VLOOKUP(E2,Sheet2!A:B,2,0)

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 .