Forum Discussion
Solved
XLOOKUP Not returning expected value
I have a simple problem that has been perplexing me for two days. I need to look up data in a column from last to first and when I find the first non-zero cell, extract the date from a different colu...
As harshulz mentioned no way to pass something like ">0" as lookup_value to XLOOKUP. If you want to "find the first non-zero cell" from last-to-first:
in B2:
=XLOOKUP(1,SIGN(I4:I35),B4:B35,Month_Start_Date,0,-1)
TheOldPuterMan difficult to visualise what you are trying to achieve, but perhaps this one will work for you.
=XLOOKUP(,L4:L35,B4:B35,Month_Start_Date,2,-1)Remove the ">0". Just leave the first argument blank but don't forget the comma!
- Riny,
No-go on that, here maybe is a better description of what I need
Month_Start_Date=03/01/2020
B4:B35 I4:I35
Work_Date Tasks_Completed
02/28/2020 1
03/01/2020 2
03/03/2020 1
03/04/2020 0
03/05/2020 4
03/06/2020 0
03/07/2020 0
=XLOOKUP(“>0”,I4:I35,B4:B35,Month_Start_Date,2,-1)
Result should = 03/05/2020 but is 03/01/2020 instead?
If I use the formula =XLOOKUP(4,I4:I35,B4:B35,Month_Start_Date,2,-1) I get 03/05/2020
Thanks, JohnTheOldPuterMan Can't check it as the formula contains a named range Month_Start_Date. Can you upload the file? Or at least the relevant part of it.
- I'll create a subset and send that. The entire file is a beast but it will be later as I need sleep. I spent last night at the ER with my wife (she's in hosp but okay) then had to work.
John
