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

MWilkesen · 05/20/24

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

bisaacs · 05/20/24

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!

bisaacs · 05/20/24

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!

MWilkesen · 05/20/24

@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 · 05/20/24

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!