Forum Discussion
xlookup not finding number/letter mix
To avoid misformatting you could use something like
=XLOOKUP(TEXT(<value>,"General"),TEXT(<lookup array>,"General"),<return array>)
Thanks for the replies.
I have since found a solution elsewhere; I used the 'Text to Columns' control on the column causing the problems. Apparently this is a common issue when importing columns from other sheets.
So far so good. In Text to Columns, and not only, it's always better to define data type. If by default Excel makes some guess, but not always correct one.
SergeiBaklan That's a terrific solution Sergei! I was having a similar problem with numerical product number with leading zeroes! Using TEXT to define the values as "General" has solved a longterm headache. Thank you.
Matthew
Matthew, glad to help
- I know this I an old post but I found this very helpful. The only problem I am having now is the file I changed my xlookups in is running extremely slowly. Like it's taking 5 minutes just to save. All of my xlookups are contained in the same file on a different sheet so it is not looking in a different file. Could this formula be the cause?
XLOOKUP itself is not dramatically slower than INDEX/MATCH or VLOOKUP in same scenarios. Depends on how do you use them, perhaps something else. If you could reproduce the case on sample file with removed sensitive data, it's better to discuss having such sample.
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.
