Check for Matching Values on Another Sheet

OK, so I have two columns on multiple sheets, ESTIMATED PROJECT SIZE and ACTUAL PROJECT SIZE. On the sheet I'm working in, I have a column called PROJECT SIZE DIFFERENCE.

Essentially, I want to write a formula in my current sheet that checks both columns on one of the other sheets (one at a time, so I'll have to write multiple formulas, I'm sure), and if those values match, return a No. If the values do not match, return a Yes.

I'm at a loss as to how to do this, but in my head it seems like it should be simple…

Thank you in advance.

Tags:

Best Answer

  • Hollie Green
    Hollie Green Community Champion
    Answer ✓

    If you add an auto row or row number column that matches up to an auto row or row number column on your current sheet can probably get away with one formula.

    =IF(INDEX(COLLECT({Column 1 on reference sheet}, {Auto row on Reference Sheet}, [Matching column for Row # on destination sheet]@row), 1) = INDEX(COLLECT({Column 2 on reference sheet}, {Auto row on Reference sheet}, [Matching column for Row # on destination sheet]@row), 1), "Yes", "No")

Answers

  • Hollie Green
    Hollie Green Community Champion
    Answer ✓

    If you add an auto row or row number column that matches up to an auto row or row number column on your current sheet can probably get away with one formula.

    =IF(INDEX(COLLECT({Column 1 on reference sheet}, {Auto row on Reference Sheet}, [Matching column for Row # on destination sheet]@row), 1) = INDEX(COLLECT({Column 2 on reference sheet}, {Auto row on Reference sheet}, [Matching column for Row # on destination sheet]@row), 1), "Yes", "No")

  • Is there any way to do it by matching data from another column? For instance, in the Index formulas on the sheet, I use a Match insert to match the PROJID on one sheet to the PROJID on the reference sheet. Is that possible?

    We're trying to go avoid adding more columns.

  • I was able to plug in what I needed to your formula and it worked. Thank you @Hollie Green !

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!