# Lookup most recent data point ignoring zeros and blank cells

#### Krucial155

##### New Member
I currently have a metric spreadsheet that is updated daily. After I put in the data, I have a function to calculate the sum and percentages of certain metrics.

I currently have a lookup function that I use for some input fields to ignore the blank cells and only return the last cell with numeric value (which will be the most recent day's data). However, when I try to use this for different metrics to pull onto another sheet, it is taking the 0s from this row. How can I have a lookup function that ignores blank cells, and ignores zeros, but still pulls the most data.

=LOOKUP(2,1/(3:3<>""),3:3)

That is the function I found for my input field. It allows us to pull the most recent cell (total followers) excluding cells in that row that are blank.

I'm very new to Excel and self-taught myself over the course of the past 2 weeks. I would love to get some guidance on how to accomplish my goal of eliminating Zeros from the lookup as well, but also understanding the function part by part, should somebody have the time.

Thank you.

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### alz

##### Board Regular
=IF(3:3>0,LOOKUP(2,1/(3:3<>""),3:3))

#### Krucial155

##### New Member
=IF(3:3>0,LOOKUP(2,1/(3:3<>""),3:3))

#### jasonb75

##### Well-known Member
=LOOKUP(2,1/(3:3<>""),3:3)
As with many things, it is much easier for us to give you more accurate guidance with a visual example that will help to avoid misinterpretation, from observations on your post though.

You don't need the (3:3<>"") part with numeric data,
Excel Formula:
``=LOOKUP(1e+100,3:3)``
will work fine and is far more efficient. Likewise, with text data you could use
Excel Formula:
``=LOOKUP(REPT("z",255),3:3)``
as long as the cells in 3:3 are empty, if the last cell contains a formula that uses "" to show a blank then that will still be picked up by the text version, the numeric one will ignore it.

To ignore zero values, you could use something like
Excel Formula:
``=LOOKUP(1e+100,1/(1/(3:3))``
which will turn any zeros or empty cells into #DIV/0! errors that the lookup will ignore, although I would advise using something like A3:Z3 instead of 3:3. Use of entire rows or columns when not needed makes formulas very inefficient and can make your formulas very slow of you have a lot of them.

#### Krucial155

##### New Member

This is the row that that function is currently working on, where I can use the function to take just the most recent cell, where it'll put it in my daily report field for Total Followers. Row 3

This is a row where I want to pull daily data and put it on another sheet for a daily, printable report. I want to take the last, non-zero field on the Daily Messages portion. Daily messages is row 27

#### Krucial155

##### New Member
@jasonb75 That seems to have worked! And I really appreciate the breakdown!

#### jasonb75

##### Well-known Member

You're welcome

Just one additional point from seeing the screen capture, the formula is doing what you asked for and looking for the last non 0 value in the row. It will not check to see it the dates in the 2 sheets match.

If you wanted to cross reference dates as well then that would need something like FILTER, INDEX or HLOOKUP depending on exactly how you want the results organised.

For information, if you check my signature block below this post, you will see a link to XL2BB which is a tool designed for the forum that lets you post small sections of your sheet in a format that you can copy and paste to excel with any existing formulas included. This is quite useful when what you need is not quite so easy to explain.

#### Krucial155

##### New Member
You're welcome

Just one additional point from seeing the screen capture, the formula is doing what you asked for and looking for the last non 0 value in the row. It will not check to see it the dates in the 2 sheets match.

If you wanted to cross reference dates as well then that would need something like FILTER, INDEX or HLOOKUP depending on exactly how you want the results organised.

For information, if you check my signature block below this post, you will see a link to XL2BB which is a tool designed for the forum that lets you post small sections of your sheet in a format that you can copy and paste to excel with any existing formulas included. This is quite useful when what you need is not quite so easy to explain.
Oh I am actually going to use this function to make a small little table, which the 2nd sheet will reference. The 2nd sheet is suppose to change each day (it's archived by a different system)

While I have you, is there an easy to convert this function to do the same thing with percentages? Making it look up the last in a row that's not blank or -100%?

Lookup the last weekly change percentage that isn't -100 (from the input 7 days ago to blank aka 0) or #div/0 result from trying 0/0.

#### jasonb75

##### Well-known Member
The numeric method will work with anything that excel sees as a number, which will include percentages, dates, times, currency amounts etc as long as they are in a valid format.

As you have 2 criteria this time though, it would be a little different. I've started off with a formula that looks at the values in A3:Z3, which you can change as needed.
Excel Formula:
``=LOOKUP(2,(1/(1/(\$A3:\$Z3))<>-100%,\$A3:\$Z3)``
I think that I have the formula correct but I haven't tested it in excel.

#### Krucial155

##### New Member
The numeric method will work with anything that excel sees as a number, which will include percentages, dates, times, currency amounts etc as long as they are in a valid format.

As you have 2 criteria this time though, it would be a little different. I've started off with a formula that looks at the values in A3:Z3, which you can change as needed.
Excel Formula:
``=LOOKUP(2,(1/(1/(\$A3:\$Z3))<>-100%,\$A3:\$Z3)``
I think that I have the formula correct but I haven't tested it in excel.
Hmmm. Yeah I get this error.

Replies
0
Views
230
Replies
2
Views
548
Replies
2
Views
52
Replies
20
Views
220
Replies
5
Views
215

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,946
Messages
5,834,521
Members
430,293
Latest member
Blind_Man

### 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.

### Which adblocker are you using?

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

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