Receiving a #REF! error on an INDEX MATCH Formula

The following formula is returning a #REF! error when I add an additional INDEX MATCH function. It works fine with just the first INDEX MATCH function. I've exhausted all options that I can think of. Here's the formula I'm using:

{=IF(AND(I21="B",J21="No"),INDEX('Bundle SKUs'!$AA$2:$AA$2494,SMALL(IF(('Bundle SKUs'!$D$2:$D$2494='8-Afdel'!A21&'8-Afdel'!$J$7)*('8-Afdel'!B21>='Bundle SKUs'!$P$2:$P$2494)*('8-Afdel'!B21<='Bundle SKUs'!$Q$2:$Q$2494),ROW('Bundle SKUs'!$AA$2:$AA$2494)-1),ROWS(1:1)))*B21)+(INDEX('Bundle SKUs'!$AA$2:$AA$2494,MATCH('8-Afdel'!A21&"---"&'8-Afdel'!$J$7&"-",'Bundle SKUs'!$A$2:$A$2494,0),IF(AND(I21="B",J21="Yes"),INDEX('Bundle SKUs'!$AA$2:$AA$2494,SMALL(IF(('Bundle SKUs'!$D$2:$D$2494='8-Afdel'!A21&'8-Afdel'!$J$7)*('8-Afdel'!B21>='Bundle SKUs'!$P$2:$P$2494)*('8-Afdel'!B21<='Bundle SKUs'!$Q$2:$Q$2494),ROW('Bundle SKUs'!$AA$2:$AA$2494)-1),ROWS(1:1)))*(B21/365)*G21)+INDEX('Bundle SKUs'!$AA$2:$AA$2494,MATCH('8-Afdel'!A21&"---"&'8-Afdel'!$J$7&"-",'Bundle SKUs'!$A$2:$A$2494,0))/365*G21))}.

I am aware of how to make this an array formula. I'm attaching the data that this formula is using.

Thanks for your help.

Tom

Instead of asking to fix existing long formula, its better if you tell us your output need from data so that somebody can give better formula for desired output.

Index gives reference error when your rows/ column value is more than database selection .

Here you used "#REF!" in formula, possible that also causing for this error.

Regards,
Suhas

I’m attempting to create a formula where I’m matching two different criteria and returning a sum of two values. The one criteria is a SKU number and the other criteria is a quantity of that SKU. Columns “P” & "Q" provide the various tiers for a given SKU. Column "B" is a list of SKU's. Also, at the end of each tier is a flat amount that needs to be added to the total calculated value. Also, if column "J" is proratable it is identified by a 1, if not it's 0. This is why I need two IF THEN statements. The end result under a "1" for proratable should take the quantity times the value in column AA for that tier plus the fixed amount at the bottom of that tier divided by 365 and multiplied by another cell which contains the number of days in the contract. The end result under a "0" for proratable should simply take the quantity times the value in column AA for that tier plus the fixed amount at the bottom of that tier. Thanks, Tom