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.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top