Add to INDEX formula to account for blank cells?

amy_ilearning

I have a mapped column that displays a Contact based on what's inputted in another cell. E.g. Slim J = Slim Julian.

Here's the formula: =INDEX({PD - NetSuite Name Mapping USE Range 1}, MATCH([PDS Selected]@row, {Formstack Name}, 0))

However, if the cell is blank, it just assigns the first Contact alphabetically, even though there's nothing to match to.

Is there a way to add to the formula above so it just keeps the mapped Contact blank if the input cell is blank?

bisaacs

Hey @amy_ilearning,

I would wrap this in an if statement, and make it so your formula only calculates if it's not blank:

=IF(ISBLANK([Formstack Name]), "", INDEX({PD - NetSuite Name Mapping USE Range 1}, MATCH([PDS Selected]@row, {Formstack Name}, 0)))

Hope this helps!

bisaacs

Hey @amy_ilearning,

I would wrap this in an if statement, and make it so your formula only calculates if it's not blank:

=IF(ISBLANK([Formstack Name]), "", INDEX({PD - NetSuite Name Mapping USE Range 1}, MATCH([PDS Selected]@row, {Formstack Name}, 0)))

Hope this helps!

amy_ilearning

You're a genius! Worked perfectly - thank you!

amy_ilearning

@bisaacs Here's another weird one that needs a blank. Instead of mapping, I'm combining the values of several cells into one cell formatted for Contacts. However, if all the cells in the range are blank, it shows #INVALID VALUE in the cell dedicated to combining the value.

Here's the formula: =INDEX(COLLECT([Core A-C]@row:[Non-Core S-Z]@row, [Core A-C]@row:[Non-Core S-Z]@row, @cell <> ""), 1)

I tried =IF(ISBLANK([Core A-C]@row:[Non-Core S-Z]@row), "", INDEX(COLLECT([Core A-C]@row:[Non-Core S-Z]@row, [Core A-C]@row:[Non-Core S-Z]@row, @cell <> ""), 1)) but I get an #INCORRECT ARGUMENT

Any ideas on how to adjust the formula so it also leaves this Contact column blank if the cells the formula is pulling from are all blank?

bisaacs

Hey @amy_ilearning,

I think the issue is ISBLANK doesn't accept a range of cells, just one cell, so we'll have to use the AND function to chain together multiple ISBLANK functions:

=IF(AND(ISBLANK([Core A-C]@row), ISBLANK([Non-Core S-Z]@row)), " ", INDEX(COLLECT([Core A-C]@row:[Non-Core S-Z]@row, [Core A-C]@row:[Non-Core S-Z]@row, @cell <> ""), 1))

Hope this helps!

amy_ilearning

Thank you so much!!