XLOOKUP Strange Rounding Error when Searching Multiple Variables

[Noel-T]

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:

=XLOOKUP(A2&B2,Table!$C$2:$C$241&Table!$D$2:$D$241,Table!$A$2:$A$241,,1)

It looks for the value in cell A2 in the range Table!C2:C241 and then searches for the value in B2 in the range Table!D2:D241. I believe both of these should be looking for an exact match (which will work every time for the A2 value) or rounding up to the next highest value (which will usually be the case for the B2 value). 

This formula works great most of the time, but when it encounters the need to round across a new tens place digit it seems to loop back. For instance, if asked to find 18.21 in the 15.5 section, it should return the value corresponding to the next highest value (20.72), instead it returns the value corresponding to 2.05. It's as though when increasing the value of the tens place in the rounding function there's a missing 0 placeholder and it reverts the correct number (2) to the ones place and finds that.

This is all being done on Windows 10 PCs using Office 365 Apps for enterprise. I'd gladly upload the document, it's not large, but there appears to be no functionality for that.

[#2 · Accepted solution]

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

[#3]

@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? 

[#4]

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