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.

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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted
  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.

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.