Sorry I didn't notice your reply.

I believe you're referring to the proposals in my sig.

Regarding [1], it just proposes that SumProduct itself reads TRUE as 1
and FALSE as 0 when it has to evaluate conditionals like:
....,(A2:A8="X"),... This has as consequence that TRUE's and FALSE's in
the sum range will also be coerced into 1's and 0's, a behavior that
would make the SUM part of SUMPRODUCT to differ from SUM().

Regarding [2], it's a proposal for extending the syntax of lookup
functions. Exmaples:

=VLOOKUP(V,Table,Idx,0,0)

which would return 0 instead of #N/A.

---------------------------------------

If you have something like:

=VLOOKUP(V,$A$2:$F$1000,3,0)

or

=IF(ISNA(VLOOKUP(V,$A$2:$F$1000,3,0)),ReturnValue, VLOOKUP(V,$A$2:$F$1000,3,0))

and the table $A$2:$F$1000 is sorted on its first column in ascending
order or you're willing to sort it on its first column in ascending
order and maintain it sorted, the following would yield more speed:

=IF(LOOKUP(V,$A$2:$A$1000)=V,LOOKUP(V,$A$2:$A$1000 ,$A$2:$A$1000),ReturnValue)

This is an efficient idiom I picked up from Charles Williams.

Sige wrote:
Hi Aladin,

Lists could indeed be an option, but my wbk should run on XL97.

-Could you show me what you mean under [1] ?
-I have a couple of lookup functions, returning an exact match ...how
do I set an optional argument returning #NA?
Does this increase the calc speed in your opinion?

Best Regards Sige


--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.