Search code examples
excelexcel-formulaexcel-indirect

Make formula Indirect

How can this formula be made with INDIRECT function so that there are no #REF errors when rows are deleted and shifted up. Here is my code:

=IFERROR(INDEX(G5:BL5, MATCH(REPT("z",255),G5:BL5)),0)
Solution

  • You don't need INDIRECT; INDEX will do just as well and is non-volatile.

    =IFERROR(INDEX(index(G:BL, 5, 0), MATCH(REPT("z",255), index(G:BL, 5, 0))), 0)

    Hard-coding row 5 will stop #REF! errors on row insertion/deletion.