Nesting Index & Countif

[M.A.Tyler]

=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

 

[Peo Sjoblom]

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]

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]

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]

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