1

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)
Jose Cortez
  • 93
  • 2
  • 10

1 Answers1

1

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.