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).
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!
Lots of weird answers: I can’t work out what Excel thinks its doing!
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?