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

Index and Match Array formula
 

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

Index and Match Array formula
 

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" wrote in message
...
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

Index and Match Array formula
 

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" wrote in message
...

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

Index and Match Array formula
 

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" wrote in message
...
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