Does MATCH handle mixed alphabetic and numeric data when sorted?

I was doing some testing on my new XLL advanced LOOKUP and MATCH functions, comparing them to MATCH when I came across some quirks (actually I think some of them are bugs).

Mixed Characters and Numbers

So suppose we have data in M3:M8, consisting of 3 numbers and 3 alphabetic characters.
The data is sorted ascending and there are no duplicates.
Note that the characters are all sorted higher than the numbers: thats the standard result with SORT.
Using MATCH with the sorted ascending option to lookup each element in the data in turn: you can see the results in column O:
so far so good – it all looks correct.

But what happens if you try looking up values that don’t exist in the data?

Since we have told MATCH that the data is sorted ascending it will use approximate match and should return the position of the largest value that is less than or equal to the lookup value.

So we can try using a set of lookup values, none of which exist in the data, which range from 0.5 (smaller than the first value in the table) through 3.5 (larger than the largest numeric value) and from a to e for the alphabetic values.

The first value 0.5 returns #N/A because there is no value in the lookup table that is smaller than or equal to 0.5.
The next value is 1.5 which lies between the first and second value so MATCH returns 1.
Now look at 3.5. since text is sorted as being larger than numbers it comes between 3 and b in the lookup table, so MATCH correctly returns 3.

Next up is a: this should also come between the 3 and the b in the lookup table, so should return 3: but it returns #N/A instead (a definite FAIL!) Similarly aa fails, but b through d work correctly.

Next up is trying larger values: dd and e are both larger than the largest value (d) in the data, so MATCH says that the largest value that is less than or equal to them is the last value.
Looking up a large number (99999) returns 3 because any text value is sorted as larger than all numbers.

Mixed Numbers and Textual Numbers

So if that does not always workl, what happens if you data contains numbers and numbers which are textual representations of numbers. (You can usually see these because they are by default left-adjusted and the numbers are right-adjusted).

When you sort this kind of data Excel asks you if you want to treat numbers that look like text as numbers.

Column F (Data 2)  is sorted this way and column D (Data 1) is sorted numbers and text separately.

So the first check is to lookup the values in the table using themselves: we should get a nice sequence from 1 to 20.
That works OK when the data is sorted numbers and text separately, but fails for the value 10 when textual numbers are sorted as numbers.

If you use lookup values as numbers from 0.5 to 10.5 incrementing by 1, you get correct answers on both tables.

But if you use lookup values entered as text by entering them as ’5.5 you get different answers to entering the numbers and then formatting them as text!

In the same way as above, column H is looking up Data 1 and column I is looking up Data 2.

Lots of weird answers: I can’t work out what Excel thinks its doing!

Conclusion

I suppose if you were extremely careful you could make this work most of the time, But for me its another good reason to avoid mixing data types within a column of a table.

Of course if all you want is an exact match you can use the unsorted option, which I think works OK because its only ever looking for equality rather than less than/greater than.

Is ‘Can of Worms’ the right description?

False Volatility – Is this a bug?

There was an interesting discussion here about using Application.Volatile False in a VBA UDF.

It seems that if you create a VBA UDF like this one:

Function functest2(x As Double) As Double
Application.Volatile (False)
Dim Z As Double
Z = x + 2
functest2 = Z
End Function

and call it from a cell when x refers to another cell that contains a volatile function or has a precedent cell with a volatile function, then the function is not recalculated when the volatile precedent cell changes because of a recalc. But of course it should!

If you change the function by either:

• Changing x as double to x as variant
• Removing the Application Volatile False statement

then the function is recalculated as expected. It also works as expected if the precedent cell is not volatile, or is changed, or a full calculation (ctrl-alt-F9) is done.

I have to admit that I had never seen the point of using Application.Volatile False since thats supposed to be what you get if you omit the Application.Volatile statement altogether. But this unexpected behaviour started me digging.

In the old XLM Macro language it turns out there was a use for Volatile False. If you created an XLM UDF using ARGUMENT(8) to make the argument remain as a reference rather than be coerced to a value the UDF was made Volatile, so you could use Volatile False if you did not want this behaviour.

Also (and probably more relevant) in a similar way when creating XLL functions if you register the function as a macro-equivalent function by adding a # to the end of the registration type string, and you are using type R or U arguments (reference) then the function will be made volatile, (the usual way is to add ! to the end of the type string). And in a similar way to the XLM macros you can use the C API function xlfVolatile to switch this volatility to False.
If you have not flagged the XLL UDF as a macro-equivalent function then using xlfVolatile has no effect
Macro-equivalent XLL functions can be useful because they have increased permissions compared to normal UDFs:

• They can get the previous value of an uncalculated cell
• They can use information functions that ordinary functions cannot

But these macro-equivalent XLL functions using R or U with Volatile set to False also do not recalculate when they have a volatile precedent.

It seems to me that this is definitely a bug with the VBA UDF, and that there is no reason to use Application.Volatile False with VBA (maybe unless you want to have a conditionally volatile function?).

With an XLL function (and an XLM function) at least there is a use for Volatile False, but the XLL behaviour with Volatile precedents also looks buggy to me.

So what do you think? Bug or by design?

Can you see another use for explicitly setting Volatile to False?

Anyone want to see if the same bug exists with an XLM function?

For more information on Volatile Functions and Actions see my Volatility page.