Conditional Formatting with a INDEX(MATCH) array formula

Hello,
I do not know if what I am trying to accomplish is possible with this formula. I have the entire formula working in a normal cell to return a TRUE/FALSE based on criteria. I cannot figure out how to translate this formula into the conditional formatting.

In the attached document:

ExecView!F8:F16 needs the conditional formatting
ExecView!J8 contains contains the example of the formula I want to use for the conditional formatting

{=IF(F8<(INDEX(tbl_Thresholds[Reserve(T3)],MATCH(A8&H8,tbl_Thresholds[SITE]&tbl_Thresholds[Man.],0))*0.5),TRUE,FALSE)}

ExecView!K8 contains only the portion of the above formula that is the variable to ensure the INDEX(MATCH) is functioning

{=INDEX(tbl_Thresholds[Reserve(T3)],MATCH(A8&H8,tbl_Thresholds[SITE]&tbl_Thresholds[Man.],0))}

Hopefully someone can assist on how to accomplish this, maybe with a different formula to get the same results.
Thanks
B

Hi,

I think it has something to do with use the Table format references such as [Reserve(T3)], [SITE] and [MAN].

Change them to column references.

=IF(F9<(INDEX(Thresholds!$D:$D,MATCH(A9&H9,Thresholds!$A:$A&Thresholds!$E:$E,0))*0.5),TRUE,FALSE)

However, referencing the entire column will really slow things down.

You can adjust them to area specific. Such as:

=IF(F9<(INDEX(Thresholds!$D$2:$D$16,MATCH(A9&H9,Thresholds!$E$2:$E$16,0))*0.5),TRUE,FALSE)

Though this can slow things down if you have a lot of them. Try a SUMIFS instead.

=IF(F8<(SUMIFS(Thresholds!$D:$D,Thresholds!$A:$A,A8,Thresholds!$E:$E,H8)),TRUE,FALSE)

Hope this is helpful.

Cheers

@southward

Thanks for the input but I like to keep my formulas as dynamic as possible (I should have mentioned that in my first post).

I try to keep my formulas very dynamic and use variables instead of references. I have found that to be best when dealing with sheets that do and will change structure frequently. I know it might not be the ideal way for best performance but, I have found it to the best way when dealing with users who do not know Excel very well.

Is there any other way to accomplish this formatting?
Thanks
B

You can use Dynamic Named Ranges that will automatically grow and include any new data.

Here is one of the DNR's I used in the attached example.

=OFFSET(Thresholds!$E$2,0,0,COUNTA(Thresholds!$E:$E)-1,1)

The DNR names I used are "Man", "ResT3" and "Site".

This formula works in the conditional formatting.

=IF(F8<(SUMIFS(ResT3,Site,ExecView!A8,Man,ExecView!H8)),TRUE,FALSE)

Hope this works for you.

Cheers