Index and Match #REF error

[DE_Tx]

Can someone take a look at the attached and help me see what is causing the #REF errors.
I have looked at this until I am cross-eyed and am not seeing it.
I am sure it is something simple.

Thanks,
T

 

[Mike H..]

Can someone take a look at the attached and help me see what is causing the #REF errors.
I have looked at this until I am cross-eyed and am not seeing it.
I am sure it is something simple.

Thanks,
T

Hi,

I prefer this way. It's an ARRAY formula, see below for how to enter it. This goes in j2 and is dragged right and down. See your attached workbook

=INDEX($D$2:$D$33,MATCH(1,($B$2:$B$33=$I3)*($A$2:$A$33=$H3)*($F$2:$F$33=J$2),0))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.

 

[DE_Tx]

Hi,

I prefer this way. It's an ARRAY formula, see below for how to enter it. This goes in j2 and is dragged right and down. See your attached workbook

=INDEX($D$2:$D$33,MATCH(1,($B$2:$B$33=$I3)*($A$2:$A$33=$H3)*($F$2:$F$33=J$2),0))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.

Thanks Mike, didn't think about using an array. I'll just need to document this so the end users don't end up breaking it.

I still can't figure out what I was doing wrong.

 

[Somendra Misra]

Hi DE_Tx,

A small tweak in @Mike H.. formula to avoid Cntrl+Shift+Enter, just wrap MATCH within SUMPRODUCT

like
=INDEX($D$2:$D$33,SUMPRODUCT(MATCH(1,($B$2:$B$33=$I3)*($H3=$A$2:$A$33)*($F$2:$F$33=J$2),0)))
in J3 and than again copy down and right.

Regards,

 

[Mike H..]

Hi DE_Tx,

A small tweak in @Mike H.. formula to avoid Cntrl+Shift+Enter, just wrap MATCH within SUMPRODUCT

like
=INDEX($D$2:$D$33,SUMPRODUCT(MATCH(1,($B$2:$B$33=$I3)*($H3=$A$2:$A$33)*($F$2:$F$33=J$2),0)))
in J3 and than again copy down and right.

Regards,

Hi,

I know you were responding to the OP and hope it's useful to him/her but I thought I would comment on your tweak, I hope you don't mind.

Wrapping the matches in SUMPRODUCT does avoid the array but comes with a calculation time overhead. It is; I think, generally accepted that array formula are slower then non-arrays but in this instance the slow execution of SUMPRODUCT negates any advantage. Using this code [LINK] to measure calculation time the non-array SUMPRODUCT execution time is ~112% of the array version. FWIW<g>

 

[DE_Tx]

@SM
Thank you SM. I'll put this one in my toolbox. Unfortunately, I have to build this on Excel 2002 so SUMPRODUCT is not available.