This is the formula I am using applied to my real tables, and it does not seem to work, it shows "not available' for all:

=IF(AND(INDEX('[EF 4012 V1 - rbi2 Lot-Retentions Register.xlsx]EF4012 V1 rbi2 LOT-RETENT REG'!H$146:K$149,MATCH([@Product],'EF 4012 V1 - rbi2 Lot-Retentions Register.xlsx'!Table1[Product],0),MATCH([@Components],'EF 4012 V1 - rbi2 Lot-Retentions Register.xlsx'!Table1[[#Headers],[Component1]:[Component6]],0))=0,D2="Yes"),"Available","Not Available")

I am also trying to remove the condition for 'product', so it will be AVAILABLE when column D 'released from production' is YES and the lot number is not on table EF 4012. And NOT AVAILABLE when column D 'release from production' is NO and/or when the lot number has been entered for in table EF 4012.

Thanks!