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.
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))
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)