Forum Discussion
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 column but the matching row.
The default to a "No Match" is the beginning of the month.
The lookup column is l4:l35 and the return column is b4:b3. B4:b35 is formatted as a short date and l4:l35 is a general format.
I'm using a Wild Card search (2) and reverse (-1) search. The formula is:
=XLOOKUP(">0",L4:L35,B4:B35,Month_Start_Date,2,-1).
l4 has a value of 1 and the latest date in l4:l35 is 02/28/2022, in l4, but the formula consistently keeps taking the "If Not Found" date of 03/01/2022.
If I change the formula to =XLOOKUP(1,L4:L35,B4:B35,Month_Start_Date,2,-1) the formula works as expected and finds the 1 in l4 thus also finding 02/28/2022.
Any help here is greatly appreciated.
TheOldPuterMan
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)
8 Replies
- i have obsserved that xlookup is not taking ">0" as number?
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)- Worked like a charm! Thanks.
And thanks to Riny for a good try
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.
