Formula needs condensing

[jamescooper]

Hello I need to condense this formula so that it calculates for +10 to -10 with this part.

Any ideas please?

MATCH(TEXT(MID(N12,6,4)+1,"0000")

IFERROR(INDEX(INDIRECT("'Compare "&$M$8&"'!$A:$A"),MATCH(TEXT(MID(N12,6,4)+1,"0000")&" "&TRIM(MID(N12,11,50)),INDIRECT("'Compare "&$M$8&"'!$C:$C"),0)),IFERROR(INDEX(INDIRECT("'Compare "&$M$8&"'!$A:$A"),MATCH(TEXT(MID(N12,6,4)+2,"0000")&" "&TRIM(MID(N12,11,50)),INDIRECT("'Compare "&$M$8&"'!$C:$C"),0)),IFERROR(INDEX(INDIRECT("'Compare "&$M$8&"'!$A:$A"),MATCH(TEXT(MID(N12,6,4)+3,"0000")&" "&TRIM(MID(N12,11,50)),INDIRECT("'Compare "&$M$8&"'!$C:$C"),0)),IFERROR(INDEX(INDIRECT("'Compare "&$M$8&"'!$A:$A"),MATCH(TEXT(MID(N12,6,4)+4,"0000")&" "&TRIM(MID(N12,11,50)),INDIRECT("'Compare "&$M$8&"'!$C:$C"),0))

Thanks.

 

[jamescooper]

Any ideas please?

Thanks in advance!

 

[jasonb75]

It should work using the same method as your previous thread,

=IFERROR(INDEX(INDIRECT("'Compare "&$M$8&"'!$A:$A"),AGGREGATE(15,6,MATCH(TEXT(MID(N12,6,4)-10+ROW(F$1:F$21)-1,"0000")&" "&TRIM(MID(N12,11,50)),INDIRECT("'Compare "&$M$8&"'!$C:$C"),0),1)),"No Match")

 

[jamescooper]

Thanks Jason, I wasn't sure with the added complexity to the formula - i.e. the Indirect/Index how this would work, but thanks.