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.

mathetes's avatar
mathetes
Silver Contributor
Jun 02, 2021

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?

 

Resources