Lookup most recent data point ignoring zeros and blank cells

Krucial155

New Member
Joined
Jan 23, 2021
Messages
8
Office Version
  1. 365
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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

alz

Board Regular
Joined
Jul 17, 2020
Messages
130
Office Version
  1. 2019
Platform
  1. Windows
How about this?
=IF(3:3>0,LOOKUP(2,1/(3:3<>""),3:3))
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,609
Office Version
  1. 365
Platform
  1. Windows
=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.
 
Solution

Krucial155

New Member
Joined
Jan 23, 2021
Messages
8
Office Version
  1. 365

ADVERTISEMENT

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
1611426741337.png


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
1611426826064.png
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,609
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jan 23, 2021
Messages
8
Office Version
  1. 365
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%?
1611428213166.png


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
Joined
Dec 30, 2008
Messages
12,609
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 23, 2021
Messages
8
Office Version
  1. 365
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.
1611430294563.png
 

Watch MrExcel Video

Forum statistics

Threads
1,129,474
Messages
5,636,536
Members
416,922
Latest member
defectexpress

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
Top