Ignoring a blank cell using an index match - GoogleSheets

kennet

New Member
Joined
Aug 9, 2019
Messages
1
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.
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,205
Office Version
365
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,370
Messages
5,486,453
Members
407,548
Latest member
FayP

This Week's Hot Topics

Top