Excel – Comparing two columns in different Excel spreadsheets

microsoft excelmicrosoft-excel-2007microsoft-excel-2010worksheet-function

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.

Best Answer

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.