# 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:

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

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.