Search:

# 2003 Match() Problem

Asked By: Jason    Date: Nov 28    Category: MS Office    Views: 2331

I have... A drop down box that refers to "Months", a named 12 cell range
with the months in them January - December.
But I also want to use the month number 1 - 12 as well.
The drop down puts the month into a cell - P1 - it.
Now I try to Match( P1 ; Months ) to get the month number in the range.

I'm getting very strange results.

For...
January -> 1
February -> #N/A
March -> 8
April -> #N/A
May -> 8
June -> 6
July -> 2
August -> #N/A
September -> 9
October -> 8
November -> 8
December -> #N/A

Share:

Help on the match  function says it has a third parameter match_type
which is assumed to be 1 if it is missing. This looks up the item or
the next larger one, but requires the array to be in order.

You could sort your array and it will probably work, or you could use
MATCH(PI,Months,0).

Another solution, example as follows.

1. In B2:B13 put the month  names, i.e. January, February, ...
2. Name these 12 cells e.g. MonthNames (select the 12 cells, Insert,
Name, Define, type in MonthNames).
3. In C2 type in =MONTH("01-" & MonthNames & "-06") and hit enter.
4. Select the 12 cells C2:C13. Hit F2 in cell  C2. Hit control-shift-
enter to create an array.
5. Select the 24 cells B2:C13. Name them MonthNumbers.
6. In cell D2 put whatever Month Name is being looked up, e.g. June.
7. In cell E2 type VLOOKUP(D2, MonthNumbers,2,False).

This solution will return the MonthNumber in E2, and does not
require that the MonthNames array be sorted. HTH.

Because of the nature of the problem... Months and all... It's not possible
to sort :-).

However... I've put the 3rd parameter in and the match  now works...
thanks... Don't know how I missed that.

I'd still like to know why the wierd results  though... ;-)

Some of the results  make sense but the weird ones are March, May, October and
November. Obviously the algorithm says they are not in the list but why, when
it can find June, and why does it then think it should choose August rather
than April.

July seems to give the logical answer because March is greater, so it takes
the previous value. But, if so, why is June not treated the same way?

It probably speeds the search by looking at halfway (June) first, If the item
is higher it looks at halfway between there and the end (September). This
means that all items between June and September alphabetically are reported as
August. Similarly July is compared with June <
March <
February > so February is selected.

Didn't find what you were looking for? Find more on 2003 Match() Problem Or get search suggestion and latest updates.

Tagged: