Highlight Rows in Sheet1 that match values in Sheet2

Question

It's been a long time since I've done anything advanced in Excel. I have Excel 2010. I've watch many videos and tried some tutorials that do sort of what I'm looking for, but I'm just missing something.

This is what I'm trying to accomplish... I have a list of about 50 SKUs in Sheet2. I have a complete list of 200 Products in Sheet1.

SHEET1:

ColA are SKUs
ColB is Desc
ColC is Price

SHEET2:

ColA are SKUs

I need a formula or Macro that will look at all SKUs in Sheet2, then find any matches in Sheet1 ColA, then highlight the rows where there is a match.

I would really appreciate any help you can provide, even if it's just a link to an exact example. Thank you!

Solution 1

If you just want to mark matching rows you can do something easy. This will return matching SKUs, or #N/A if no match:

=VLOOKUP(A2,Sheet2!$A:$A,1,FALSE)

If you really want highlighting you could use the helper formula above and set up conditional formatting (CF) over the range. The CF formula will be something like

=NOT(ISNA($D2)) (assuming you put the VLOOKUPs in column D)

There is another way to do CF that uses no helper formula. First you need to set up a named range on Sheet2 A:A. I'll call it SKUs in this example.

Then a CF formula like this will tag matching rows:

=MATCH($A2,SKUs,0)>0

Edit: I am assuming the data (and CF range if you use that) starts in row 2, allowing for a header in row 1.

Solution 2

This may be a little late, but I figured I would still add my 2 cents. I use the following formula to do something similar... =IFERROR(IF(VLOOKUP(B1,Sheet2!$A:$A,1,FALSE)>0,"Y","N"),"N")

Basically I just have a column of Y or N for if that item is also on "Sheet2" and this is the formula that decides whether it is a Y or N.

Solution 3

Just use your VLOOKUP() along with IFERROR() in a conditional format formula.

Select the range you would like to apply conditional formatting then do Home -> Conditional Formatting (in 2007) then "Apply a formula".

Then you'll want to apply a formula more or less like this one:

=IF(IFERROR(VLOOKUP($A2, Sheet2!$D$2:$D$4, 1, 0)), 0, 1)

Just adapt the ranges to your needs. I tested this to work on 2007.

Comments

[Spencer]

Thanks everyone for the great examples. Unfortunately I must be missing something, when I enter the formula in "Format values where this formula is true:" I get the error "The formula you typed contains an error". I can't get past this error. If I remove the leading equal sign the dialog closes without an error, but the conditional formatting doesn't display.

[Siraj Samsudeen]

@Spencer, can you please post a screenshot of the formula box where you are entering the value. I am not sure whether you can post your file here in SO. Then, I can figure out the exact problem