IFERROR Formula Should Catch INDEX() Returning #INVALID COLUMN NAME

Vojtech Gajdos

Recently we noticed that when we have a function that looks like this:

=IFERROR(INDEX(COLLECT(...), Index), "")

If the Index value was 0 the result of INDEX() was an error (understandably) - #INVALID COLUMN NAME, however IFERROR() did not catch this as and error but was also returning #INVALID COLUMN NAME. This makes not much sense, since I don't think this is a "improperly constructed formula" but rather a legitimate situation. In our case the detailed scenario was:

We needed to extract the rows from a range, that met certain criteria and from that range return the last member. In some cases non of the rows met the criteria and so the resulting range was empty, as was it's length. So the INDEX formula in fact - in that case - was "INDEX(<empty range>, 0)".

The whole formula itself looked like this:

=IFERROR(INDEX(COLLECT({Range}, {Condition_Range}, Condition), COUNTM(COLLECT({Range}, {Condition_Range}, Condition))), "")

I propose that in this case IFERROR does recognize this as an error and returns the error replacement value.

Regards

Vojtech