Lookup and compare two cell values

[Blunder1]

Hi,
I have to reconcile two sheets within a workbook. There should be a common reference on most cells in column A, so i use the below formula to lookup and match to return my answer, where there isn't a common reference the returnng #N/A value tells me I need to investigate why. The below array formula works ok but is slow as there are around 8000 rows. Is there a faster way to replace the below using vba?
Thanks in advance
Blunder

{=INDEX(ODS!$A$2:$A$37,MATCH(1,(ODS!$A$2:$A$37=NTPA!A2)*(ODS!$N$2:$N$37=NTPA!M2)*(ODS!$O$2:$O$37=NTPA!N2),0))}

 

[DonkeyOte]

I would suggest you concatenate Cols A, N & O on ODS sheet into a single delimited string at which point you can dispense with the Array.

ODS!Z2:
=$A2&"^"&$N2&"^"&$O2
copied down to Z37

Your subsequent formula is then a basic INDEX & MATCH which should prove relatively efficient.

=INDEX(ODS!$A$2:$A$37,MATCH(NTPA!A2&"^"&NTPA!M2&"^"&NTPA!N2,ODS!$Z$2:$Z$37,0))

Obviously use of Col Z is purely for demo. purposes - modify as appropriate.