Thanks, Biff. This is precious. I didn't know a negative number returns TRUE. Wouldn't have thought of testing with a negative number.

You won't believe how much I learned about SUMPRODUCT in the last couple of days - to be specific, data type matching. SP is not as forgiving as COUNTIF/SUMIF. I may want to coerce both sides of = or <> with &"" to be safe.

In case you want to respond to this, please go to

http://groups.google.ca/group/micro...016cd?lnk=st&q=&rnum=3&hl=en#be68f092dc3016cd
I don't want to "hijack" this thread as this is a different subject now.

Thanks again for helping me sort out one-dimensional MATCH( ) etc.

Epinn

Biff said:

the lookup_value can be an array provided

SUMPRODUCT( ) is called, right?

You can use any function that handles arrays: (array entered)

=OR(ISNUMBER(MATCH(A1:B2,D1

6,0)))

A single cell can be the lookup_array in any MATCH( ), right?

Yes

We still won't label the MATCH( ) two dimensional. Correct?

Correct. The lookup_array must be a 1 dimensional array.

A general question on IF( ):

As long as the condition returns a value greater than 0,

then it is TRUE, right? It doesn't have to be 1.

It can be ANY NUMBER that is not 0:

A1 = -100

=IF(A1,TRUE)

An empty cell will evaluate as 0. A text entry will return #VALUE!

Biff

Max,

Good job! This is fascinating. I was once told that MATCH( ) is one

dimensional. When I first saw $C$1:$E$3 as an argument in the MATCH( ), I

thought two dimensional was happening. Then, when I looked deeper, I

realized that the two-dimensional array is actually the lookup_value and not

the lookup_array. The lookup_array is a single cell - F1, G1 and H1

respectively. I have never seen anything like this. So, the lookup_value

can be an array provided SUMPRODUCT( ) is called, right? A single cell can

be the lookup_array in any MATCH( ), right? I tested it and it seemed to be

the case. Please enlighten/educate me if you don't mind. We still won't

label the MATCH( ) two dimensional. Correct?

A general question on IF( ):

As long as the condition returns a value greater than 0, then it is TRUE,

right? It doesn't have to be 1. Please correct me if I am wrong.

I am wondering if another solution would be INDEX(MAX(ROW etc. I may be

off.

Enjoy your Monday!

Epinn

You're welcome, Luke !

Thanks for the feedback

--

Max

Singapore

http://savefile.com/projects/236895
xdemechanik

---