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>)
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.
Thanks for the reply. Gotcha on the VALUE() function. Either way, I'm guessing that the application of a function will incur a performance hit; especially if the number of records involved is large.
Now, for defining the datatype of an Excel column, I'll admit to assuming that formatting a column was actually changing the datatype of the column. I think you're saying that's NOT the case, correct? If so, what actually defines the datatype of a specific Excel column?
What actually lead me to this thread was a scenario where I had some formulas based on other worksheets. I changed some of those sheets (same data columns and types, so I thought) to pull data from a database so as to refresh them when necessary. That's when the formulas stopped working and I got the error I mentioned above. The ONLY way that I was able to get it to work is with the TEXT function that you mentioned above. Yes, it actually worked with the VALUE() function, but it was dog slow. The TEXT function worked without a noticeable performance hit.
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
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.
