comparison - Compare between Excel worksheets

All my product's SKU and prices are in an Excel document in different columns (Sheet1). In the same spreadsheet, but on another worksheet (Sheet2), I have distributor data with product SKU and prices.

Format

Sheet1 | A = sku, B = product price
Sheet2 | A = sku, B = product price

I'm looking for a way to compare all the prices in Sheet1 with the prices in Sheet2. If the prices are not the same, the field should be green.

The SKU number never changes.

Can anyone point me in the right direction?

Answer

1. 
   

   On Sheet1, in cell C1, enter the following formula, which uses VLOOKUP: to find the corresponding price on Sheet2.

   
   

   = VLOOKUP(A1, Sheet2!A:B, 2, FALSE)

   
   


2. 
   

   Make sure that Sheet2 is sorted by SKU, or VLOOKUP won't work right.

   
   


3. 
   

   Select column B on Sheet1, select Format->Conditional Formatting, change "Cell Value Is" to "Formula Is", and enter

   
   

   = B1 <> C1

   
   

   If you want the cell to turn green when there's no matching SKU, use this instead:

   
   

   = IF(ISNA(C1), TRUE, B1 <> C1)

   
   


4. 
   

   Click on format and select the green formatting you want for the mismatch cells.

   
   


5. 
   

   Hide column C, if you want.