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?**

When Excel looks up a value in a list it ignores others data types, so if it’s looking up a text value any numeric, boolean or error values are treated as if they were blank. Maybe not a bug but it can be confusing especially since it means formulas and sort commands can produce conflicting results.

In the first example looking up “a” returns an error because the smallest text value in the list is “b”. Changing the numbers to text using Text to Columns > Next > Next > Text does return 3, but formatting as text doesn’t change the data type until you reenter the value as can be checked using the TYPE function.

In the second example the text version of “10” is out of place for the match formula, it should be between the “1” and “2”. Again this is because text numbers are treated different from normal numbers a simple example is the formula =”1″=1 which returns FALSE.

Other functions like COUNTIF can also produce quirky results.when there are numbers formatted as text in the range, eg the criteria “=1” and “<=1" can return different values. This is more a bug than a feature in my view.

Lori,

Your explanation sounds correct.

Still looks like a can of worms to me.

My version of MATCH treats text as larger than numbers and so avoids some of this disaster area.

If you have a column with mixed data types viz Real numbers(123), Text Numbers(‘123), Text and Blanks then the default order of sort would be Numeric-Alpha-Blank

So first the numbers followed by text numbers, followed by text, followed by blanks

Remarks

14

23

66

‘184

’39

’41

’71

ABC

XYZ

(1st Instance of blank)

(2nd Instance of blank)

Do we then conclude that (blank)>Text>TextNumbers>Numbers ?

Many functions in Excel would treat blanks as zeros.

So there is a clear mismatch in how comparisons are done by features v/s formulas