Index and Match Array formula

Can someone tell me why the first array formula returns the correct
value, and the second one returns #NUM. The array size increases so I
don't want to limit the size as the first formula does, and thought the
second formula would allow this. Once again I would really value some help.

{=INDEX(A2:A87,MATCH(1,(G93=C2:C87)*(G94=B2:B87),0))}


{=INDEX(A:A,MATCH(1,(G93=C:C)*(G94=B:B),0))}

Kind regards,
Graham Haughs
Turriff
Scotland

Array formulas don't like whole columns.

Graham Haughs wrote:
>
> Can someone tell me why the first array formula returns the correct
> value, and the second one returns #NUM. The array size increases so I
> don't want to limit the size as the first formula does, and thought the
> second formula would allow this. Once again I would really value some help.
>
> {=INDEX(A2:A87,MATCH(1,(G93=C2:C87)*(G94=B2:B87),0))}
>
> {=INDEX(A:A,MATCH(1,(G93=C:C)*(G94=B:B),0))}
>
> Kind regards,
> Graham Haughs
> Turriff
> Scotland

--

Dave Peterson

>Array formulas don't like whole columns.

Ir depends on how the formula is structured and what function is calling the
entire column:

=INDEX(A:A,MATCH(1,(B1:B20=100)*(C1:C20=1000),0))

Index is using the entire column.

=INDEX(A:A,MATCH(1,(B1:B20=MAX(B:B))*(C1:C20=MAX(C:C)),0))

Max is using the entire column.

Also, (although this isn't a real good example), entire columns can be used
in nested functions within Sumproduct:

=SUMPRODUCT(SUMIF(A:A,100,B:B))

Biff

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> Array formulas don't like whole columns.
>
> Graham Haughs wrote:
>>
>> Can someone tell me why the first array formula returns the correct
>> value, and the second one returns #NUM. The array size increases so I
>> don't want to limit the size as the first formula does, and thought the
>> second formula would allow this. Once again I would really value some
>> help.
>>
>> {=INDEX(A2:A87,MATCH(1,(G93=C2:C87)*(G94=B2:B87),0))}
>>
>> {=INDEX(A:A,MATCH(1,(G93=C:C)*(G94=B:B),0))}
>>
>> Kind regards,
>> Graham Haughs
>> Turriff
>> Scotland
>
> --
>
> Dave Peterson

Thanks for taking the time to explain it clearly.

Graham

Biff wrote:
>>Array formulas don't like whole columns.
>
>
> Ir depends on how the formula is structured and what function is calling the
> entire column:
>
> =INDEX(A:A,MATCH(1,(B1:B20=100)*(C1:C20=1000),0))
>
> Index is using the entire column.
>
> =INDEX(A:A,MATCH(1,(B1:B20=MAX(B:B))*(C1:C20=MAX(C:C)),0))
>
> Max is using the entire column.
>
> Also, (although this isn't a real good example), entire columns can be used
> in nested functions within Sumproduct:
>
> =SUMPRODUCT(SUMIF(A:A,100,B:B))
>
> Biff
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
>
>>Array formulas don't like whole columns.
>>
>>Graham Haughs wrote:
>>
>>>Can someone tell me why the first array formula returns the correct
>>>value, and the second one returns #NUM. The array size increases so I
>>>don't want to limit the size as the first formula does, and thought the
>>>second formula would allow this. Once again I would really value some
>>>help.
>>>
>>>{=INDEX(A2:A87,MATCH(1,(G93=C2:C87)*(G94=B2:B87),0))}
>>>
>>>{=INDEX(A:A,MATCH(1,(G93=C:C)*(G94=B:B),0))}
>>>
>>>Kind regards,
>>>Graham Haughs
>>>Turriff
>>>Scotland
>>
>>--
>>
>>Dave Peterson
>
>
>

Thanks for the clarification.

Biff wrote:
>
> >Array formulas don't like whole columns.
>
> Ir depends on how the formula is structured and what function is calling the
> entire column:
>
> =INDEX(A:A,MATCH(1,(B1:B20=100)*(C1:C20=1000),0))
>
> Index is using the entire column.
>
> =INDEX(A:A,MATCH(1,(B1:B20=MAX(B:B))*(C1:C20=MAX(C:C)),0))
>
> Max is using the entire column.
>
> Also, (although this isn't a real good example), entire columns can be used
> in nested functions within Sumproduct:
>
> =SUMPRODUCT(SUMIF(A:A,100,B:B))
>
> Biff
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > Array formulas don't like whole columns.
> >
> > Graham Haughs wrote:
> >>
> >> Can someone tell me why the first array formula returns the correct
> >> value, and the second one returns #NUM. The array size increases so I
> >> don't want to limit the size as the first formula does, and thought the
> >> second formula would allow this. Once again I would really value some
> >> help.
> >>
> >> {=INDEX(A2:A87,MATCH(1,(G93=C2:C87)*(G94=B2:B87),0))}
> >>
> >> {=INDEX(A:A,MATCH(1,(G93=C:C)*(G94=B:B),0))}
> >>
> >> Kind regards,
> >> Graham Haughs
> >> Turriff
> >> Scotland
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson