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...)
18 Replies
- 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>)I just recently had this issue and looked high and low for a solution. What's weird is that you can define the datatype for the column to "General", but it still doesn't work for the cells that have only numbers. I was getting something like "the value is not available to the formula or function". And attempting to evaluate the formula is of no help, because it doesn't trace completely - very frustrating. So thanks a bunch for your help. BTW, I saw another post that worked as well, but it used the VALUE function to convert to "General". For some reason that function was dog slow. I saw another reply to you below about your solution slowing down a users spreadsheet as well. I'm sure it's due to the running of the function. Still weird to me that you'd have to do this conversion if you've already defined the datatype of the column - some bug within Excel maybe?
ccheatha , all depends on goals.
VALUE() converts texts which looks like numbers into numbers and returns #VALUE! error for the texts. That's okay if you try to find numbers only even if they are initially in text form.
Applying General or any other format to the column you don't change values data type. You may check result with ISNUMBER() or ISTEXT(). Formatting is only formatting, nothing else, that's by design. Values are converting from one type to another by formulae, or Paste Special operation, or conversion like Data->Text to Columns.
For example in Power Query is opposite situation. We may change data type for the column, with that default for data type format will be applied. Format in some cases could be change by formulae, e.g. if we convert date to text.
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.
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.
