Index and Match Array formula

[Graham Haughs]

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.

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

 

[Biff]

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

 

[Graham Haughs]

Thanks for taking the time to explain it clearly.

Graham

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]

Thanks for the clarification.

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