I have a spreadsheet that reviews payment file names and values that needs formatting to highlight specific lines where the file exceeds a set value based of the file name & its storage location, as there can be some cross over amongst the names and drop locations.

Table 1

Table 2

The file name will change daily for every payment sent but they will have core components in each of them that is included in a list alongside its maximum payment value.

I need the conditional formatting to compare the value of the value in Table 1 against Table 2 and highlight if it exceeds that listed highest value, but only if the file name string contains the matching user number and the File Storage in Table 1 matches the Match Criteria in Table 2.

I have tried using an index match to complete this with the below formula but it highlights everything that exceeds the value in the first line of Table 2 instead of making sure they have a matching value for there specific file name.

=$B2>=INDEX('Table 2'!$C:$C,MATCH($C2,'Table 2'!$D:$D,1),MATCH(1,INDEX(COUNTIF($A:$A,"*"&'Table 2'!$D$1&"*"),),0))

This formula is then repeated for B3, B4, B5 etc. depending on how many payments are processed that day to try and cover all eventualities. The date in Table 2 currently has 17 different criteria but this will increase as new payment types are added for new systems.