Jun 02 2021 06:48 AM - edited Jun 02 2021 06:49 AM
Jun 02 2021 06:48 AM - edited Jun 02 2021 06:49 AM
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?
Jun 02 2021 09:00 AM - edited Jun 02 2021 09:06 AM
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?
Jun 02 2021 09:53 AM - edited Jun 02 2021 09:54 AM
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:
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.
Jun 02 2021 10:28 AM
Agreed. The lookup array still comprised numbers despite being number-formatted as text. Re-entering the numbers changed them to text and the XLOOKUP then works (the little green triangle warning the user of numbers held as text also appears at that point).
A non-volatile alternative to your use of INDIRECT would to use a further XLOOKUP to return the column to search,
= XLOOKUP(CodArt1, Table1[H1], XLOOKUP(E4, Table1[#Headers], Table1) )
Jun 02 2021 11:01 AM - edited Jun 02 2021 11:17 AM
INDIRECT is a tricky function. Wonderful once you get it to work, but tricky. I think you might have the quotation marks in the wrong place, or too many. You'll figure it out.
Here's an example of one that I have that, once all is said and done, refers to a different tab, a tab that may or may not have a special character in it, and then a specific cell within that tab.
You'll notice that the cell references there do NOT have quotes around them. Let me break that down a bit (hoping it won't overwhelm with detail).
The result of all those concatenations will be something like this: =AAPL_CC!C3. That is to say, that INDIRECT (which appears a summary sheet in a workbook tracking investments) goes to an individual sheet where I track an investment in an Apple Covered Call, to retrieve the value in cell C3 of that sheet, and bring it back to a summary sheet tracking all of the investments in various companies. I copy that INDIRECT down a column so that it picks up different names of different equities, pointing to different sheets, but always bringing back cell C3 in each of those sheets.
P.S. Added after @Peter Bartholomew added his reference to volatility: I have quite a few of those INDIRECT functions in a 1.7 MB file, and it still performs all its recalculations in a split second. No noticeable delays.
Jun 02 2021 11:10 AM
It was here in techcommunity that I learned what "volatile function" means. @Peter Bartholomew 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?!"
Jun 02 2021 12:42 PMSolution
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) )
=XLOOKUP( ""&CodArt3, ""&Table3[H1], XLOOKUP(E26,Table3[#Headers],Table3) )
which works for combination of texts and numbers.