Forum Discussion
Solved
XLOOKUP Strange Rounding Error when Searching Multiple Variables
My colleague and I are attempting to use XLOOKUP to search an array of values for two variables and return a third, rounded up to the next largest value. The formula we came up with for this is: =XL...
Noel-T Not sure that what you see as correct results are in fact correct, though merely a coincidence. When you use A2&B2, you are concatenating the two numbers into a text. So, if A2 contains the number 10.5 and B2 contains 18.21, XLOOKUP will look for the text "10.518.21" in the texts concatenated from C2:C24 & D2:D24. Hence, you are no longer working with numbers.
Noel-T Not sure that what you see as correct results are in fact correct, though merely a coincidence. When you use A2&B2, you are concatenating the two numbers into a text. So, if A2 contains the number 10.5 and B2 contains 18.21, XLOOKUP will look for the text "10.518.21" in the texts concatenated from C2:C24 & D2:D24. Hence, you are no longer working with numbers.
Riny_van_Eekelen Thank you very much. I was afraid that something was happening with the multiple lookup variable, but I couldn't figure out what it might be. You've confirmed that the multiple variable lookup isn't working like we thought.
Since xlookup works fine when looking at a single variable, I wonder if there is a way to make it do what we're trying.
*edit
I spoke with my colleague and it turns out there's more data that may backup our original assertion. If we divide the B and Table!D column values by 1000 (any number that moves all the numbers to the right of the decimal eg. 18.21 becomes 0.01821 and the next highest value becomes 0.02072) our formula works as intended, selecting the appropriate corresponding A column values. We've tested this with a range of variables and it is working perfectly. Would this not suggest that the problem actually lies in the transition between values in the tens place?
Noel-T would need the file to see what you are trying to do. If you can't upload it in the regular view, try sending it to me via a direct message.
