MATCH output error #N/A

[Guest]

Using Excel 2002. I am trying to use MATCH statement:
=MATCH(1,($A$1:$I$1=2003)*($A$2:$I$2="Clm"),0)

Row 1 has years (more than one of each), and Row 2 is one of three headings,
like this:
A B C D E F
G H I
Row 1: 2001 2001 2001 2002 2002 2002 2003 2003 2003
Row 2: Pm Clm O/S Pm Clm O/S Pm Clm
O/S

When I click the fx button to the left of the formula bar, the expression is
correctly evaluated to give the output 8, but in the cell the output is
instead #N/A.

Hopefully my reasoning for using the expression is clear, but to explain, it
is in order to pick out the column for a particular year and type of data
(Pm, Clm or O/S). Any suggestions on how to get Excel to output the answer it
knows but won't display would be much appreciated!

Thanks in advance...

 

[Bernie Deitrick]

Rich,

That is an array formula, so enter it using Ctrl-Shift-Enter instead of just
enter.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Okay, think I've established that the problem was that I didn't enter it using

CTRL+SHIFT+ENTER

What a strange and obscure reason - Excel help gave no hint whatsoever to
that being a possible solution...