Ignoring a blank cell using an index match - GoogleSheets

[kennet]

Hi,

I am using an index match formula that needs to ignore blanks.

Originally I was using the following formula:
=iferror(index('secret'!$E:$E,match($B12,'secret'!$L:$L,0)),"")

My data has multiple repeats within the L column but sometimes the corresponding E value is blank and despite the next L repeat having content within the E cell, a blank will be returned unless i change the index match formula.
I have an if formula stating that if a blank is found, to carry on doing the formula from a point which I can specify. So for example if a blank match was found at row 5, I would want to have the formula keep working from row 6.

=IF(index('secret'!$E:$E,match($B13,'secret'!$L:$L,0))="",index('secret'!$E33:$E,match($B13,'secret'!$L:$L,0)),index('secret'!$E:$E,match($B13,'secret'!$L:$L,0)))

The above formula works since I'd found the blank row (using another formula - see below) to be row 32. I have it looking from row 33 if a blank was found. Ideally, I wouldn't need to manually put in the 33 and could just insert the below formula in place of the 33;

ROW(index('secret'!$E:$E,match($B13,'secret'!$L:$L,0)))+1

How might I combine the two?

Also, if there is a better way of doing an index match which ignores blank values, I'd very much like some input as this seems slightly convoluted. This is all on Google sheets btw in case thats relevant.

 

[Peter_SSs]

Welcome to the MrExcel board!

This is all on Google sheets btw in case thats relevant.

It is extremely relevant since you had posted your question in the Excel Questions forum.
Hence it has been moved to the "General Excel Discussion & Other Questions" forum