CODEWITHSUNDEEP
excelexcel-formulaexcel-2010vba
Spencer
Spencer

Reputation: 83

Highlight Rows in Sheet1 that match values in Sheet2

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!

Upvotes: 4

Views: 57371

Answers (4)

ApplePie
ApplePie

Reputation: 8942

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.

Upvotes: 1

andy holaday
andy holaday

Reputation: 2302

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.

Upvotes: 5

cr1msonmyst
cr1msonmyst

Reputation: 21

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.

Upvotes: 2

Siraj Samsudeen
Siraj Samsudeen

Reputation: 1694

You can use conditional formatting in Excel 2010 to achieve what you want to do.

There are 2 ways to do this. The first one works only with the ranges in one sheet whereas the second one lets you work across sheets.

  1. Assuming you can copy and paste both the ranges in one worksheet, you can select both ranges using Control key. After selecting the ranges, Go to Home->Conditional Formatting->Highlight Cell Rules->Duplicate Values. Now Select Duplicate in the dialog box and it should highlight the names in Range 1 that are appearing in Range 2 (your original SKU list).

  2. If you can't copy and paste the second range into the same worksheet, then you have to use a formula with conditional formatting. Select the used range in ColA in sheet1, Go to Home->Conditional Formatting->New Rule. Now Select the Rule Type 'Use a formula to determine which cells to format'. Now type the formula like this (this formula assumes that your cursor is in A1 when you apply invoke the dialog)

    =COUNTIF(Sheet2!$A$1:$A$3,Sheet1!A1)

That should highlight in Sheet1 all the items found in Sheet2. Edit the above formula to include the correct range for your situation and use a dynamic range if you know how to do it.

I have used approach 1 countless times and I have just tested approach 2 with a sample and it works. If they don't work for you, please let me know and I can help you further.

Upvotes: 0

Related Questions