I am trying to use a formula that uses INDEX function. In order to generalize for all the lines that sometimes may contain information from another sheet and sometimes they may not.

That's why I get an ISREF error within the cells because the corresponding cells in the other sheet can't be referenced.

I want to display a default value instead of the ISREF error message. I tried using ISREF function itself within an IF condition but it doesn't work on the same cell. It only references another cell because it is a cell checking function and it doesn't check the output of a formula.

I tried also ISERROR function but it didn't work also. Here's a snippet of the formula that I am putting within my cell:'

INDEX(Plagesuivi; $Q203; 9)

• Plagesuivi is a named range
• $Q203 contains the row number (that I fetch dynamically and correctly)
• 9 is the column number

P.S. The indexing is working fine with cells that do appear is the named range: Plagesuivi

CodePudding user response：

I would go with iferror() like this:

=iferror(INDEX(Plagesuivi;$Q203;9);0)

Or replace the 0 with ""

CodePudding user response：

After trial and error the best way to avoid all sorts of errors is:

= IF($Q203=""; 0; IFERROR(INDEX(Plagesuivi; $Q203; 9); 0))

• IFERROR checks for all sorts of unpredictable errrors it is a safe-guard against unpredicted cases where it takes 0 by default.

• IF in the second case checks whether the referencing content of Q203 is empty or not, in case it is empty the cell takes 0 by default else it gets the output of the false case formula.