INDIRECT last text value in range

Hi everyone!

I currently have a formula that seeks out the last text value in a range, my range starts from row 5 all the way to row 500, from column G to BL. Rows are deleted by a macro and the remaining data is shifted up, this is causing a #REF error. How can I modify my formula with INDIRECT to not have a #REF error when rows are deleted. Here is my current formula


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

Try this:

=IFERROR(LOOKUP(REPT("z",255),INDIRECT("G5:BL5")),0)

that did the trick! Thank you!