Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

2 Answers 2

use a VLOOKUP:

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

You would put this in every row on sheet 1 where there is a row of data, adjacent to the data. It would look at the adjacent ID and then look for that ID in sheet 2. It would then return the value it finds.

=VLOOKUOP(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 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 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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.