Forum Discussion
xlookup not finding number/letter mix
I have a column consisting of part numbers: a mix of numbers and digit/letter combos, e.g 2, 3, 3a, 4, 5, 6, 6a, 6b, 6c, 7, 8 ...... I have formatted both the source column and the lookup column a...
To avoid misformatting you could use something like
=XLOOKUP(TEXT(<value>,"General"),TEXT(<lookup array>,"General"),<return array>)
Hello sir
About the code you provided, can you explain what "TEXT" does?
Maybe you can put in a little example
=XLOOKUP(TEXT(<value>,"General"),TEXT(<lookup array>,"General"),<return array>)
Having mix of texts and numbers it's more reliable to use only texts. TEXT(value, "General") or TEXT(value, "@") converts number to text and keeps text as it is. For example
Please check the same in attached file.