Forum Discussion
XLOOKUP Strange Behaviour
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.
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.
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.
=INDIRECT($B8&IF($C8="","","_"&$C8)&"!"&J$1)
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).
- $B8 refers to a cell that contains text that, in many cases, is the name of the tab
- There are times, however, when that name is repeated but with different extensions, SO..
- ... $C8 refers to a cell that may or may not contain the rest of the tab's name; if it's blank, then my INDIRECT does not add anything to the text in $B8, but if there's content in $C8, then INDIRECT adds an underscore plus whatever content is in $C8.
- So all of that is to create the correct Tab name
- It then adds the exclamation point and yet another cell reference, J$1, which itself contains the cell reference desired on the tab, the name of which was created in those first components.
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 PeterBartholomew1 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.