XLOOKUP: A Surprising Failure
The function XLOOKUP is a great improvement over VLOOKUP, but there is a surprising quirk about it. It boggles my mind and kind of leaves me unmoored.
Background
This whole exercise stems from a desire to learn about how Monte Carlo simulations could be set up, so I used a book that incorporated Excel with Monte Carlo simulation. I learn best through examples and the book had some examples.
One example had a very basic profitability exercise with exchange rates thrown in.
Figure 1: Units sold, unit price in Australian $, and unit cost remain fixed. The only thing that will be changing in this exercise will be the exchange rate A$/U$.
The goal of this exercise is to vary the exchange rate and see how the profitability varies.
To do the Monte Carlo, a Data Table was used.
Figure 2: The Data Table in this figure is next to the basic set up and is formed via Data – What if Analysis – Data Table in the menu system. The column input for the data table is the exchange rate in C6 and the formula in cell H3 references cell C14.
Column G4 through G19 starts off with a hard input of .84 and then increases by .01. So, cell G5 has the formula =G4+.01.
That’s the simple set up.
The set up was actually a bit more complicated and I ran into some problems with the data table producing weird results. Consequently, I ended up creating a simpler problem (which is figure 2) to narrow down what could be causing the weird results.
It was during the process of figuring out what was causing the weird results that I ran into weird results with XLOOKUP. It has not been a good day with excel problem.
The first encounter of XLOOKUP failure
I love XLOOKUP because it is such a simpler function that makes a lot of sense with the tool tip providing me how to use it. It has always worked well for me.
Figure 3: This figure is almost like figure 2 but I added the XLOOKUP line in row 15. I wanted to double check that the data table off to the right produced the same profitability calculations as the basic set up did in columns B:D.
Basically, I was making sure that I had set up the data table properly.
In this example, it looked like I did. Everything is matching with the same profitability figures.
Figure 4: But look at what happens when I set the exchange rate in cell C6 to .94. Cell C15 gives me “na”, the part of the XLOOKUP that gives when it can’t find the lookup value. You see .94 in the data table but XLOOKUP is telling me it can’t find it!
This was the situation when cell G14 was set up with the formula =G13+.01.
Figure 5: Now watch what happens when I hardcode cell G14 with .94: XLOOKUP can find .94 in the data table again and provide the associated profitability calculations.
I don’t really know what is causing this error from XLOOKUP.
First check for the cause of the error
The first thing that came to mind to check was to see if cell G14 with the formula =G13+.01 is the same as cell C6.
Figure 6: The check to see if cell C6 = G14 can be found in row 19, in columns B and C.
It tells me that the number .94 are the same in both cells; they are both equivalent.
Second check with a formula instead of data table
With the idea that maybe the data table was the source of the problem, I decided to replace the data table with a profitability formula.
Figure 7: Nope, didn’t work. Cell C15 with the XLOOKUP formula still gave me “na”.
The example of profitability formulas can be found in column J which is showing the formulas used in column H.
I enlarged that image a little in the hopes you can see the formulas better.
Third check with VLOOKUP instead of XLOOKUP
This one gets interesting because maybe the problem is not just with XLOOKUP.
Figure 8: And VLOOKUP didn’t work!
There is something about going from .93 to .94 with a formula of =G13+.01 in the data table set up that is just causing everything to go haywire.
A fix for maybe a “floating point” problem
So, I finally googled and found a possible answer at the Stack Overflow site. A person had used VBA to generate the numbers in place of a formula such as =G13+.01. Here’s the VBA code for generating the numbers.
For Each myCell In myRange
myCell.Value = myID
myID = myID + 0.1
Next myCell
The line “myID = myID + .1” is similar to my =G13+.01
The suggested solution was to use rounding:
myID = Round(myID + 0.1, 1)
So, here’s my version of the rounding formula:
Figure 9: XLOOKUP finally works!
The highlighted area under column I gives an example of the rounding formula set up in column G.
The Stack Overflow site where I found the possible solution is https://stackoverflow.com/questions/69639488/xlookup-function-wont-detect-an-exact-match-until-i-place-the-cell-in-edit-mode
Takeaway
My takeaway is sometimes XLOOKUP or VLOOKUP, and maybe other formulas, will fail under very simple situations. This is really surprising. You may have to do some data type fixing, use CLEAN or TRIM functions, or use ROUND.
If those suggested solutions don’t work, go googling or maybe use AI to see if it can figure out what ails Excel.
This was one of the simpler situations but with huge weird results. It’s a mystery.
I don’t know if it’s my machine or my version of the Microsoft 365 that is causing this hiccup, but it is sure weird.
You must be logged in to post a comment.