Forum Discussion
XLOOKUP Strange Behaviour
I'm trying to use XLOOKUP function using an excel table where I make the search. This table as the first column formatted as Text as it will acomodate data that needs to be formatted this way. The XLOOKUP uses a cell as input for the lookup value. Depending on the way this cell is formatted the XLOOKUP function behaves differently. If the input cell is formatted as General, everything works as it should. But if I format the cell as Text (because I need to input a numeric string with leading zeros as data), then the XLOOKUP returns #N/A...
I've prepared a small Excel file to demonstrate the situation.
Could anyone help me?
As a comment, if both lookup value and lookup column are texts, when this works
=XLOOKUP( CodArt3, CHAR(173)&Table3[H1], XLOOKUP(E26,Table3[#Headers],Table3) )Better
=XLOOKUP( ""&CodArt3, ""&Table3[H1], XLOOKUP(E26,Table3[#Headers],Table3) )which works for combination of texts and numbers.
7 Replies
- Would it not be easiest to format the cell to show leading zeroes? And still be numeric?
As a comment, if both lookup value and lookup column are texts, when this works
=XLOOKUP( CodArt3, CHAR(173)&Table3[H1], XLOOKUP(E26,Table3[#Headers],Table3) )Better
=XLOOKUP( ""&CodArt3, ""&Table3[H1], XLOOKUP(E26,Table3[#Headers],Table3) )which works for combination of texts and numbers.
It is working now. I'm not sure but I think that you might have had an (invisible) character in at least one of the cells involved in the text version of the lookups.
One of the biggest traps, and it messes people up all the time, is typing in text, say, as "1 " --which to the human eye looks the same as "1"...but isn't at all the same, and the computer doesn't see it as the same.
I basically re-entered the texts in the table where you're performing the lookup.
By the way, for a demonstration file, this has some of the most elaborate functions--using INDIRECT, etc.--is all of that really necessary in whatever it is that you're creating?
Many thanks for your help.
I've noticed that you inserted new values in the data column and that help me to find the problem.
I didn't have any space or anything else before. The problem is (perhaps) that if I change the format of the column keeping the old data there, it assumes the initial data "type".
In your file if you:
- Erase the content of the first column.
- Change the column to data format "Number".
- Reinsert a new set of data (i.e. 1, 2, 3).
- Reformat the column once again to data format "Text"
You get the same problem as before.
So my conclusion is that cannot insert data in the column before formating the column to the data type you want.
Regarding the INDIRECT function. Well... it was the only way I had to XLOOKUP accept a concatened string as a function parameter. In the work I am doing, I define dynamically the column where I want to get the returning data. That's the reason. I've tried to use a simple string concatenation (like i.e. "Table1["&E5&"]" but that didn't result. Don't know why...
Thanks once again.
It was here in techcommunity that I learned what "volatile function" means. PeterBartholomew1 has given you a "non-volatile" alternative...basically a straight XLOOKUP...to perform your lookup. I just wanted to assure you that "volatile" in Excel terminology doesn't mean the same thing as having, say, a volatile business partner, one you never know how he/she is going to respond.
Volatile Excel functions end up taking more time because they recalculate every time--EVERY TIME--a calculation is performed, whether or not any of their references have changed. So in a really large spreadsheet, that can become a performance issue. But it's not as though sometimes they'll say 2+2=4, but other times 2+2 = 3.1416, or "who cares? what 2+2 equals?!"
http://www.decisionmodels.com/calcsecretsi.htm
