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.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,612
Office Version
  1. 365
Platform
  1. Windows
Oops, I knew what I was trying to do but looks like I made a bit of a mess of it:oops:
Excel Formula:
=LOOKUP(2,1/((1/(1/$A3:$Z3))<>-100%),$A3:$Z3)
Tested this time to make sure it works :)
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Krucial155

New Member
Joined
Jan 23, 2021
Messages
8
Office Version
  1. 365
Oops, I knew what I was trying to do but looks like I made a bit of a mess of it:oops:
Excel Formula:
=LOOKUP(2,1/((1/(1/$A3:$Z3))<>-100%),$A3:$Z3)
Tested this time to make sure it works :)
Thank you so much Jason.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,499
Messages
5,636,687
Members
416,935
Latest member
Atulcp

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