function details show value, but display shows #n/a

[Guest]

Here is my simple test formula:
=MATCH(TRUE,C18:E18>0,0)
c18= 0
d18= 1
e18=2
The details of the fx show the following:
Lookup-value= TRUE
Lookup-array=(FALSE,TRUE,TRUE)
match type=0
Formula result=2 ,which is the response I want.

My sheet answer, however is #n/a while it should be 2. I don't know why.
Please help

 

[Guest]

To make that formula work you need to indicate to Excel that an array is
being used.

Try this:
Edit the formula cell, but make no changes
Commit that array formula by holding down the [Ctrl][Shift] keys when you
press [Enter].

Excel will put curly braces { } around the formula and you should see the
result you want. Note: you cannot just type in the braces...you have to let
Excel put them in.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Guest]

Great and fast reply. Works exactly as suggested. Many thanks, Mike

To make that formula work you need to indicate to Excel that an array is
being used.

Try this:
Edit the formula cell, but make no changes
Commit that array formula by holding down the [Ctrl][Shift] keys when you
press [Enter].

Excel will put curly braces { } around the formula and you should see the
result you want. Note: you cannot just type in the braces...you have to let
Excel put them in.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

Here is my simple test formula:
=MATCH(TRUE,C18:E18>0,0)
c18= 0
d18= 1
e18=2
The details of the fx show the following:
Lookup-value= TRUE
Lookup-array=(FALSE,TRUE,TRUE)
match type=0
Formula result=2 ,which is the response I want.

My sheet answer, however is #n/a while it should be 2. I don't know why.
Please help