find first non blank cell in a row

M

mdmarsh

New Member
Joined
Sep 4, 2008
Messages
16
I'm back again. Hopefully, I won't be bothering you all much after this.

I need to know the value (which is a date) of the first non-blank cell in a range in a row. I have found some array formulas, but they either return it for the whole row (which I don't want), or they return text (which I don't want), or they don't work at all.

The range is: B9:FL9. Some of the cells in this range are blank, some are "0", and some contain dates. I need to know the most recent (furthest left) cell that contains a date and what that date is. Thanks!
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
If it really is the case that some cells are blank and others contain zeros and others contain dates, and you want to find the first (leftmost) date in the range B9:FL9, this would do that...remember to format the formula-containing cell as a date format so the serial number that gets returned by the formula is in a recognizable date format.

This is not an array formula, just enter it using the Enter key.

=INDEX(B9:FL9,MATCH(TRUE,INDEX((B9:FL9<>0),0),0))
 
Upvote 0
Thanks. That is much closer than anything else I have found. However, it only works on a row with no blank cells. If there is a blank cell ("") before the most recent date, it returns a blank cell. If there are only zeros and dates, it returns the most recent date. Did I do something wrong?
 
Upvote 0
It works for me with blank cells, so now let's define what "blank cell" means to you.

Is the cell you are saying is blank only "looking blank" because it does not display anything?

Or, really and truly, is the cell not empty, meaning maybe you have a null string formula in it, or a stray spacebar character, or anything (anything means anything) that is in the cell which you are claiming is blank.

Details please.
 
Upvote 0
The cell contains an IF function that returns "" if another cell is zero, so the cell contains "" b/c the cell it is referencing (on another worksheet=0)
 
Upvote 0
mdmarsh said:
The cell contains an IF function that returns "" if another cell is zero, so the cell contains "" b/c the cell it is referencing (on another worksheet=0)
Click to expand...

Try this
=INDEX(B9:FL9,MATCH(1,IF(B9:FL9<>0,IF(B9:FL9<>"",1)),0))

Ctrl+Shift+Enter
 
Upvote 0
Absolutely PERFECT!! Thank you so much. You've save me hours and hours. I'm new to this, but it has been fun figuring out all excel can do! Thanks for your help.
 
Upvote 0
mdmarsh said:
The cell contains an IF function that returns "" if another cell is zero, so the cell contains "" b/c the cell it is referencing (on another worksheet=0)
Click to expand...
OK, so you have null string formulas in the range, meaning non-empty cells...

Edit, I see snasui just posted a formula, which is an array (Ctrl+Shift+Enter) that works for me, so it looks like you should be good to go.
 
Upvote 0
What If I have a Date Range that goes across the Top with the Lower being on the Left and the Higher being on the Right and I want to select the most recent one. IS there any way to do this without flipping the date range
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Formula to find first blank cell and return another value
Replies
4
Views
53
Fluff
Fluff
N
  • Question
Formula help to search a table for a date greater than today that has a non-blank cell and return the date in a separate cell.
Replies
2
Views
169
Alex Blakenburg
A
L
  • Solved
Returning a date in a cell, but leave blank if no date entered
Replies
8
Views
261
Peter_SSs
Peter_SSs
B
  • Solved
Find last row of data in cell A that is a part of a table
Replies
7
Views
89
Alex Blakenburg
A
velohead
  • Solved
vlookup and return from same row
Replies
6
Views
287
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,136
Messages
6,123,251
Members
449,093
Latest member
Vincent Khandagale

Share this page

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