I have a large excel table that I am looking to automate calculations when data is added or updated. Xlookup (and vlookup as well) don't return all the values in the lookup table. I am asking excel to calculate a lookup value into one cell, look up the associated value in the lookup table, then return the value in another cell.
The problem that I am running into is that sometimes excel returns the value, and other times it returns an NA (or nothing with xlookup) when looking up the exact same value in the exact same table, being called in the exact same way. It appears to be the same values that excel cannot lookup in a given column. I have verified that the cells are both formatted the same, tried using logical expressions to look for leading or trailing spaces or hidden characters and didn't find any, and tried deleting the values in the lookup table or the request column and entered them again manually, still with no luck.
Below are screenshots from my data table and lookup table:
The formula in column U that correctly calls the value from Vlookup2:
=XLOOKUP(S14,ToFigureK!A$8:A$68,ToFigureK!E$8:E$68, "",0,1)
The formula in column T that cannot call the value from Vlookup1:
=XLOOKUP(R15,ToFigureK!A$8:A$68,ToFigureK!E$8:E$68, "",0,1)
A snippet of the lookup table that both formulas are referencing:
Does anyone have any thoughts as to why excel cannot look up all values?
Screenshots of =S14=R15:
The problem may be arising from the formula in the R column. The R and S columns have an IFS formula to round column Q up and down to the nearest 10th. The equation in R15 is =IFS(Q15 > 0, ROUNDDOWN(Q15,1), Q15 < 0, ROUNDUP(Q15,1)). If I replace the reference to R15 in the equation in T15 with the value that it is referencing, XLOOKUP is able to lookup the value:
Change from:
=XLOOKUP(R15,ToFigureK!A$8:A$68,ToFigureK!E$8:E$68, "",0,1)
to
=XLOOKUP(-0.8,ToFigureK!A$8:A$68,ToFigureK!E$8:E$68, "",0,1)
The equation in R15 is no different from the equations above or below. I tried to simplify the spreadsheet by inserting the formula from column R into the XLOOKUP formula, and it did not solve the problem.
It appears that with certain values, excel simply doesn't want to read the calculated value. Thoughts?