xlookup not finding number/letter mix

[quilkin]

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

[#2]

@quilkin 

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.

[#3]

@quilkin 

To avoid misformatting you could use something like

=XLOOKUP(TEXT(<value>,"General"),TEXT(<lookup array>,"General"),<return array>)

[#4]

@Sergei Baklan 

@Riny_van_Eekelen 

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.

[#5]

@quilkin 

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.

[#6]

@Sergei Baklan 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

[#7]

@onethreeone 

Matthew, glad to help

[#8]

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.

[#9]

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

[#10]

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

[#11]

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?

[#12]

@Kslaz 

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.

[#13]

@Sergei Baklan 

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

[#14]

@MPELINE 

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.