Comparing Columns on Two Different Sheets

KD Harrigan
KD Harrigan ✭✭✭
edited 02/02/23 in Smartsheet Basics

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?

Answers

  • Ryan Sides
    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?

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 02/03/23

    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • KD Harrigan
    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å
    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.