XLOOKUP Strange Behaviour

[MrBig1964]

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?

[#2]

@MrBig1964 

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?

[#3]

@mathetes 

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:

1. Erase the content of the first column.
2. Change the column to data format "Number".
3. Reinsert a new set of data (i.e. 1, 2, 3).
4. 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.

[#4]

@MrBig1964 

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)
  )

[#5]

@MrBig1964 

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 @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.

[#6]

@MrBig1964 

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?!"

http://www.decisionmodels.com/calcsecretsi.htm

[#7 · Accepted solution]

@MrBig1964 

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.

[#8]

Would it not be easiest to format the cell to show leading zeroes? And still be numeric?