INDEX MATCH with multiple values question


I have 2 sheets.

Response Form

Assignment tracker

Both sheets have the same following columns:

Evaluator Name

Candidate Name

I have a check box column in Assignment Tracker that I'd like to check off if the Candidate name AND the Evaluator name match in both sheets.

There will be duplicate results for each column but the pair should always be unique. The formula I have doesn't work as it only checks the very first instance of either and stops there. Is there a way to write a formula that will stop once BOTH values are present?

Here's my formula:

=IFERROR(IF([Evaluator Name]@row = INDEX({FY24 Hirevue Response Sheet Range 1 }, MATCH([HV Candidate Name]@row, {FY24 Hirevue Response Sheet Range 2}, 0)), 1), "")

Best Answer

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓

    I think I would just use a COUNTIFS in this situation:

    =IF(COUNTIFS({FY24 Hirevue Response Sheet Range 1}, [Evaluator Name]@row, {FY24 Hirevue Response Sheet Range 2}, [HV Candidate Name]@row) > 0, 1, "")


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!