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 as text. But xlookup() only finds the cells which have both digits and letters (i.e. 3a, 6a, 6b, 6c...)
- A common problem with VLOOKUP is a mismatch between numbers and text. Either the first column in the table contains lookup values that are numbers stored as text, or the table contains numbers, but the lookup value itself is a number stored as text. ... The result is the #N/A error, even though 3 is clearly in the table.
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.
- 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.
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 suspect that in one column your data was entered as numbers and text and then you formatted the columns as text. Numbers are left-justified but they are still numbers. Your other column probably consists of "real" text. Note that, when you first format the column as text and then enter data, all entries will become text. Check the nature of your data by entering =ISNUMBER(cell). TRUE means that it is a number, FALSE means that it is not. This is demonstrated in the attached file, though I used VLOOKUP as it will have the same effect.
Riny_van_Eekelen thanks for the explanation, which format are we trying to achieve to make it work? I use text to columns on every column, still getting the N/A when I use the formula though. When I use the =ISNUMBER(cell) I get TRUE on most columns, FALSE on one column, and don't know which one is correct.
BCURRIE2240 Not sure that I understand how your question relates to this (rather old) thread.
Perhaps better to start a new and provide some screenshots or share a file, clarifying what you need.
