• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need Help with XLOOKUP

R

RDaga

Member
Hi Friends, this is the first time I am using XLOOKUP, it is returning the value but the place where it should give an error it is returning Points. I tried searching a lot but was unable to find anything specific to this issue.


1715341681993.png
 
Hi,
Seems the "error" is in the approximate match criterion of formula. With this setup, your lookup table is sorted on the key value, I believe a binary search is preferred as it speeds up calculation.
It's generally a bad practice to reference entire columns, and you can use a spilling array formula in cell [C2]
=IF(B2:B1665="";"";XLOOKUP(B2:B1665;E1:E41;F1:F41;"";-1;2))
 

Attachments

  • Sample(5).xlsx
    37.1 KB · Views: 2
Who says the formula should return an error? The text "Days Bucket" is part of the column you are searching and, being text, it is treated by Excel as being larger than any number. Hence it returns "Points" perfectly correctly.

Just to back up @GraH - Guido 's comment, I timed the 1457 calculations you performed at just under 30 sec. By comparison, the array formula
Code: 
= XLOOKUP(days, daysBucket, points, "Out of range", 1)
took 4ms to obtain the same results.
 
You must log in or register to reply here.
Back
Top