Comparing Columns on Two Different Sheets

KD Harrigan

I have two sheets that I need to compare data and highlight any matching data. If Sheet "A" query column matches any of the data in Sheet "B" query column, then the matching data in Sheet "B" query column needs to highlight. I've not had any luck finding a formula that works. Any thoughts?

Ryan Sides

@KD Harrigan

Hey KD, you'll want to create another column (type: checkbox) in Sheet B and use the Index/Match formula combination.

After you add the column in Sheet B, right click and Manage References. In there, you'll create a reference to your query column in Sheet A. Name it "Sheet A".

Then in your new column in Sheet B, enter this:

=if(index({Sheet A}, match([SheetBQueryColumnName]@row, {Sheet A}, 0)) = [SheetBQueryColumnName]@row, 1, 0)

When you get a match on the above formula, the checkbox will check on. You can then create a Conditional Format on this new column to highlight the row. Does that help?

Andrée Starå

Hi @KD Harrigan

I hope you're well and safe!

To add to Ryan's excellent advice/answer.

Another option could be to use COUNTIFS.

Try something like this.

=IF(COUNTIFS({Sheet A},[SheetBQueryColumnName]@row) > 0, 1)

Did that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!

KD Harrigan

This is awesome, I would have never figured this out on my own, this works perfectly!!

Thank you both for your help.

Be well, be happy, and be safe!

Have a wonderful weekend! :)

Andrée Starå

@KD Harrigan

Excellent!

You're more than welcome!

✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!

KD Harrigan

I'm having trouble once again with matching columns on two different sheets. I've tried using the formulas provided after adding the check box column and setting the reference, but they both return unparseable results. I want to match up the company names on these two separate sheets and highlight the company name on sheet B (TT) if it's found on sheet A. Any help is appreciated.