Forum Discussion

MrBig1964's avatar
MrBig1964
Copper Contributor
Jun 02, 2021
Solved

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 XL...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Jun 02, 2021

    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.

MrBig1964's avatar
MrBig1964
Copper Contributor
Jun 02, 2021

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.

mathetes's avatar
mathetes
Silver Contributor
Jun 02, 2021

MrBig1964 

 

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

 

Resources