Index | Match: How to Return Specific Value in Blank Cells

Options

Hello! I am using an Index & Match formula to populate the following fields based on the chosen Integrated Participant: Group, RHMID, Class of Trade, and GPO Start date.

I want any blank cells (aka, data not assigned to an Integrated Participant) to display #NO MATCH.

I am using IFERROR statements (below) that will return #NO MATCH if an error occurs, but I am wondering if I need to change my formula to an IF statement or if there is an alternative way to return the desired #NO MATCH when a cell in the lookup sheet is blank.

=IFERROR(INDEX({Index Table | GPO Start}, MATCH([Integrated Participant]@row, {Index Table | Facility}, 0)), "#NO MATCH")

I welcome any and all ideas!

THANKS!

Michael

Best Answer

  • bisaacs
    bisaacs ✭✭✭✭✭
    Answer ✓
    Options

    Hey @MWilkesen,

    Ohhh got it! Then maybe try something like this:

    =IF(OR(ISBLANK([Class of Trade]@row), ISBLANK([GPO Start Date])), "#NO MATCH", IFERROR(INDEX({Index Table | GPO Start}, MATCH([Integrated Participant]@row, {Index Table | Facility}, 0)), "#NO MATCH"))

    Using IF with the OR/ISBLANK functions should return what you're hoping for. You can also add other rows/fields within the OR function for any other ranges that you're looking out for blank fields.

    Hope this helps!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

Answers

  • bisaacs
    bisaacs ✭✭✭✭✭
    Options

    Hey @MWilkesen,

    Could you maybe wrap it in an IF(ISBLANK()) function?

    =IF(ISBLANK([Integrated Participant]@row), "#NO MATCH", IFERROR(INDEX({Index Table | GPO Start}, MATCH([Integrated Participant]@row, {Index Table | Facility}, 0)), "#NO MATCH"))

    See if that works!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • MWilkesen
    MWilkesen ✭✭✭
    Options

    @bisaacsThanks for the feedback. Yes, that works if my Integrated Participant field is blank. However, I wasn't as straightforward as I was in my thought process; I am given the INTEGRATED PARTICIPANT, and I used that to look up all other fields. What I need is to have any data filed return a #NO MATCH that is missing data on the lookup sheet (i.e., lines 3-6 have populated the GROUP & RHMID, but there is no matching data for CLASS OF TRADE and GPO START DATE, this is where I need the #NO MATCH to populate. I hope that I am not as obtuse as in my initial question.

    Thanks!

    Michael

  • bisaacs
    bisaacs ✭✭✭✭✭
    Answer ✓
    Options

    Hey @MWilkesen,

    Ohhh got it! Then maybe try something like this:

    =IF(OR(ISBLANK([Class of Trade]@row), ISBLANK([GPO Start Date])), "#NO MATCH", IFERROR(INDEX({Index Table | GPO Start}, MATCH([Integrated Participant]@row, {Index Table | Facility}, 0)), "#NO MATCH"))

    Using IF with the OR/ISBLANK functions should return what you're hoping for. You can also add other rows/fields within the OR function for any other ranges that you're looking out for blank fields.

    Hope this helps!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • MWilkesen
    MWilkesen ✭✭✭
    Options

    @bisaacs,

    THANK YOU!!! The solution was staring me in the face! I greatly appreciate your assistance!!!

    Michael

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!