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.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
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.

Resources