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>)
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.
