If(IsBlank) function combined with Index Match

Guest Experience Shared Services

I am trying to write a formula that includes an IF(IsBlank) and Index Match formula.

If the Hotel Code is blank, I do not want to display Hotel Info. I have tried the two following formulas and received a #Incorrect Argument Set error

1. =(ISBLANK([Inn Code]@row, INDEX({SystemSize and Pipeline Current Range 1 hotel info}, MATCH([Inn Code]@row, {SystemSize and Pipeline Current Range 2 Inn Code}, 0)), ""))
2. =IF(ISBLANK([Inn Code]@row, INDEX({SystemSize and Pipeline Current Range 1 hotel info}, MATCH([Inn Code]@row, {SystemSize and Pipeline Current Range 2 Inn Code}, 0)), ""))

The hotel info cell is referencing a difference sheet.

When I remove the If IsBlank portion of the formula the index match works. An Inn Code may not be assigned for all of the Hotel Info rows. Those cells are blank. When that happens SmartSheet locates the first Blank Inn Code and assigns the Hotel info for the blank.

Thoughts?

Leibel S

You had a slight syntax issue. Try this:

=IF(ISBLANK([Inn Code]@row), INDEX({SystemSize and Pipeline Current Range 1 hotel info}, MATCH([Inn Code]@row, {SystemSize and Pipeline Current Range 2 Inn Code}, 0)), "")

Janae G.

Hello! Your formula is almost there, just need to move some parentheses and commas around a little. The ISBLANK() should be the logical expression portion of your IF formula, then the INDEX MATCH portion of the formula is the value if false. Your value if true is simply "" to return a blank cell if Hotel Code is blank.

Try this:

=IF(ISBLANK([Inn Code]@row), "", INDEX({SystemSize and Pipeline Current Range 1 hotel info}, MATCH([Inn Code]@row, {SystemSize and Pipeline Current Range 2 Inn Code}, 0)))

Alternatively, you can have your IF statement check if the Inn Code is not blank. In that version of the formula the INDEX MATCH would be value if true. This is probably what you were going for in your original formula, so you just needed to add the NOT() formula and move parentheses.

=IF(NOT(ISBLANK([Inn Code]@row)), INDEX({SystemSize and Pipeline Current Range 1 hotel info}, MATCH([Inn Code]@row, {SystemSize and Pipeline Current Range 2 Inn Code}, 0)), "")

Hope this helps!