INDEX MATCH MATCH with Contacts List

Hello,

I'm trying to use index match match to pull contacts based on 2 different columns. Many of the contacts pull up but there a few that show #INVALID VALUE. I checked to make sure the data was filled properly and everything looks good. Anything I can do to fix this?

My formula is: =INDEX({Program Area Contacts Range 1}, MATCH(Discipline@row, {Program Area Contacts Range 2}, MATCH(Market@row, {Program Area Contacts Range 3})))

Result:

When it should be pulling:


Appreciate the help!

Best Answer

  • Smartsheet User 99
    Smartsheet User 99 ✭✭✭
    Answer ✓

    First, when it comes to matching multiple criteria, I personally find using MATCH MATCH gets messy, so I usually use INDEX(COLLECT() formula.

    =INDEX(COLLECT({Meh Range 1}, {Meh Range 2}, Discipline@row, {Meh Range 3}, Market@row), 1)

    Formula Breakdown

    Contact Sheet

    Master Sheet

    I hope this helps out.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!