Nesting Index & Countif
 

=INDEX(COUNTIF('PAR CALC.'!A3:A133,B3),'PAR
CALC.'!C3:C133,MATCH(Answers!L4,'PAR CALC.'!AV3:AV133,0))

Is this the proper way to nest these two functions? It returns a #REF!
error, but is it due to the formula or the calculation?

Thanks.

M.A.Tyler

Nesting Index & Countif
 

Maybe you should explain hat you are trying to do instead, that formula make
no sense to me,
why are you even trying to include COUNTIF?


--


Regards,


Peo Sjoblom


"M.A.Tyler" <Great Lakes State wrote in message
...
=INDEX(COUNTIF('PAR CALC.'!A3:A133,B3),'PAR
CALC.'!C3:C133,MATCH(Answers!L4,'PAR CALC.'!AV3:AV133,0))

Is this the proper way to nest these two functions? It returns a #REF!
error, but is it due to the formula or the calculation?

Thanks.

M.A.Tyler

Nesting Index & Countif
 

My apologies for the confusion, I'm a bit of a novice.

There are two worksheets, "Answers" and "PAR CALC." I would like to populate
Answers D4 with the data from PAR CALC. C3:C133, that matches Answers B3,
There is however a second match criteria, PAR CALC. AV3:AV133 needs to match
Answers L4. I thought if I could somehow isolate the matches from the second
criteria first, count only if (Countif). Then I could simply do an Index
Match for the first criteria. This way I would have just one formula, perhaps
thats not possible?

Thanks for your help, and patience.

Mike.

"Peo Sjoblom" wrote:

Maybe you should explain hat you are trying to do instead, that formula make
no sense to me,
why are you even trying to include COUNTIF?


--


Regards,


Peo Sjoblom


"M.A.Tyler" <Great Lakes State wrote in message
...
=INDEX(COUNTIF('PAR CALC.'!A3:A133,B3),'PAR
CALC.'!C3:C133,MATCH(Answers!L4,'PAR CALC.'!AV3:AV133,0))

Is this the proper way to nest these two functions? It returns a #REF!
error, but is it due to the formula or the calculation?

Thanks.

M.A.Tyler

Nesting Index & Countif
 

Try

=INDEX(C3:C133,MATCH(1,(A3:A133=B3)*(AV3:AV133=L4) ,0))

I removed your sheet names to reduce the clutter but you should see what to
do

This is an array formula and needs to be entered with ctrl + shift & enter
as opposed to just enter


--


Regards,


Peo Sjoblom


"M.A.Tyler" <Great Lakes State wrote in message
...
My apologies for the confusion, I'm a bit of a novice.

There are two worksheets, "Answers" and "PAR CALC." I would like to
populate
Answers D4 with the data from PAR CALC. C3:C133, that matches Answers B3,
There is however a second match criteria, PAR CALC. AV3:AV133 needs to
match
Answers L4. I thought if I could somehow isolate the matches from the
second
criteria first, count only if (Countif). Then I could simply do an Index
Match for the first criteria. This way I would have just one formula,
perhaps
thats not possible?

Thanks for your help, and patience.

Mike.

"Peo Sjoblom" wrote:

Maybe you should explain hat you are trying to do instead, that formula
make
no sense to me,
why are you even trying to include COUNTIF?


--


Regards,


Peo Sjoblom


"M.A.Tyler" <Great Lakes State wrote in message
...
=INDEX(COUNTIF('PAR CALC.'!A3:A133,B3),'PAR
CALC.'!C3:C133,MATCH(Answers!L4,'PAR CALC.'!AV3:AV133,0))

Is this the proper way to nest these two functions? It returns a #REF!
error, but is it due to the formula or the calculation?

Thanks.

M.A.Tyler

Nesting Index & Countif
 

Peo,
Thanks for the help and patience, it worked great first try!

"Peo Sjoblom" wrote:

Try

=INDEX(C3:C133,MATCH(1,(A3:A133=B3)*(AV3:AV133=L4) ,0))

I removed your sheet names to reduce the clutter but you should see what to
do

This is an array formula and needs to be entered with ctrl + shift & enter
as opposed to just enter


--


Regards,


Peo Sjoblom


"M.A.Tyler" <Great Lakes State wrote in message
...
My apologies for the confusion, I'm a bit of a novice.

There are two worksheets, "Answers" and "PAR CALC." I would like to
populate
Answers D4 with the data from PAR CALC. C3:C133, that matches Answers B3,
There is however a second match criteria, PAR CALC. AV3:AV133 needs to
match
Answers L4. I thought if I could somehow isolate the matches from the
second
criteria first, count only if (Countif). Then I could simply do an Index
Match for the first criteria. This way I would have just one formula,
perhaps
thats not possible?

Thanks for your help, and patience.

Mike.

"Peo Sjoblom" wrote:

Maybe you should explain hat you are trying to do instead, that formula
make
no sense to me,
why are you even trying to include COUNTIF?


--


Regards,


Peo Sjoblom


"M.A.Tyler" <Great Lakes State wrote in message
...
=INDEX(COUNTIF('PAR CALC.'!A3:A133,B3),'PAR
CALC.'!C3:C133,MATCH(Answers!L4,'PAR CALC.'!AV3:AV133,0))

Is this the proper way to nest these two functions? It returns a #REF!
error, but is it due to the formula or the calculation?

Thanks.

M.A.Tyler