Index Match alternative?

[288enzo]

Hello,

I'm currently using an array, but the time it takes is much longer than I would like.

Cells G2:G964 I have

=INDEX(Sheet0!$E:$E,MATCH(1,(Sheet0!$H:$H=$F2)*(Sheet0!$B:$B=G$1),0))

Cells H2:H964 I have

=INDEX(Sheet0!$E:$E,MATCH(1,(Sheet0!$H:$H=$F2)*(Sheet0!$B:$B=H$1),0))

Cells I2:I964 I have

=INDEX(Sheet0!$E:$E,MATCH(1,(Sheet0!$H:$H=$F2)*(Sheet0!$B:$B=I$1),0))

Cells J2:J964 I have

=INDEX(Sheet0!$E:$E,MATCH(1,(Sheet0!$H:$H=$F2)*(Sheet0!$B:$B=J$1),0))

Cells K2:K964 I have

=INDEX(Sheet0!$E:$E,MATCH(1,(Sheet0!$H:$H=$F2)*(Sheet0!$B:$B=K$1),0))

Cells L2:L964 I have

=INDEX(Sheet0!$E:$E,MATCH(1,(Sheet0!$H:$H=$F2)*(Sheet0!$B:$B=L$1),0))

Cells M2:M964 I have

=INDEX(Sheet0!$E:$E,MATCH(1,(Sheet0!$H:$H=$F2)*(Sheet0!$B:$B=M$1),0))

Cells N2:N964 I have

=INDEX(Sheet0!$E:$E,MATCH(1,(Sheet0!$H:$H=$F2)*(Sheet0!$B:$B=N$1),0))

Cells O2:O964 I have

=INDEX(Sheet0!$E:$E,MATCH(1,(Sheet0!$H:$H=$F2)*(Sheet0!$B:$B=O$1),0))

Cells P2:P964 I have

=INDEX(Sheet0!$E:$E,MATCH(1,(Sheet0!$H:$H=$F2)*(Sheet0!$B:$B=P$1),0))

Cells Q2:Q964 I have

=INDEX(Sheet0!$E:$E,MATCH(1,(Sheet0!$H:$H=$F2)*(Sheet0!$B:$B=Q$1),0))

Cells R2:R964 I have

=INDEX(Sheet0!$E:$E,MATCH(1,(Sheet0!$H:$H=$F2)*(Sheet0!$B:$B=R$1),0))

Cells S2:S964 I have

=INDEX(Sheet0!$E:$E,MATCH(1,(Sheet0!$H:$H=$F2)*(Sheet0!$B:$B=S$1),0))

Cells T2:T964 I have

=INDEX(Sheet0!$E:$E,MATCH(1,(Sheet0!$H:$H=$F2)*(Sheet0!$B:$B=T$1),0))

Cells U2:U964 I have

=INDEX(Sheet0!$E:$E,MATCH(1,(Sheet0!$H:$H=$F2)*(Sheet0!$B:$B=U$1),0))

Cells V2:V964 I have

=INDEX(Sheet0!$E:$E,MATCH(1,(Sheet0!$H:$H=$F2)*(Sheet0!$B:$B=V$1),0))

In a nut shell, copying G2 to G2:V964, but the Array formula doesn't allow for that

The above Arrays are returning a date (MM/DD/YYYY). There are blank cells in the referenced (Sheet0!$E:$E) and the result returned is 1/0/1900. I would prefer blank results to return "N".

Any pointers would be very much appreciated.

BTW Sheet0 has 15,456 rows.

Thank you

 

[Fluff]

You should never use whole column references in a formula, especially an array formula, unless you want your workbook to be very slow.
Try

=IFERROR(1/(1/INDEX(Sheet0!$E$2:$E$20000,MATCH(1,(Sheet0!$H$2:$H$20000=$F2)*(Sheet0!$B$2:$B$20000=G$1),0))),"N")

 

[288enzo]

You should never use whole column references in a formula, especially an array formula, unless you want your workbook to be very slow.
Try

=IFERROR(1/(1/INDEX(Sheet0!$E$2:$E$20000,MATCH(1,(Sheet0!$H$2:$H$20000=$F2)*(Sheet0!$B$2:$B$20000=G$1),0))),"N")

Worked like a charm, thank you for the good tip not to use whole columns in my references.

 

[Fluff]

You're welcome & thanks for the feedback.