06 Aug 2008, 00:00

Compare four columns in Open Office or Excel

For example, you have 2 sheets, Sheet1 and Sheet2: they both have a list of part numbers in column A and a list of prices in column B. You want to compare the prices for the same part numbers in each sheets. One way to do this is to copy the prices from Sheet1 to Sheet2 in a third column and then use conditional formatting to highlight the different prices:

Copy this formula in cell C2 of Sheet2 (and then drag it down to the cells below):

=INDEX($Sheet1.A$2:B$3001;MATCH(A2;$Sheet1.A$2:A$3001;0);2)

This formula assumes that there is a maximum of 3000 different products in Sheet1 (starting at cell A2).

Sample file:compare_four_columns.ods