Index Match Conditional formatting

[kbloggs21]

I have two tables 

There are for instances unique employee ID in look up column and then dates like on column DB8.

Table 1 - 

Table 2 - is the holiday table it matches the employee Id and date 

I tried to conditional format: 

=INDEX('Leave Chart - Weekly - Auto'!$I$1:$AI$293,MATCH('Forecast Data (Current) - HOL'!$E161,'Leave Chart - Weekly - Auto'!$A$1:$A$293,0),MATCH('Forecast Data (Current) - HOL'!DF$8,Table_Leave_Chart___Weekly___Auto[[#Headers],[01/07/2023]:[30/12/2023]],0))>8

What it shows is array area, match the employee id to table 1 match the date to table 2 , if greater than 8 highlight red so it trying to say if employee id 2 and 29/06/2023 books 16 hours that week. On the forecast employee id 2 and 29/06/2023 with be conditioned formatted red. But if employee id 2 on holiday table 2 had zero holidays booked on 30/06/2023 the forecast employee id would not be highlighted on 30/06/2023.

Could someone advise as nothing is working with formula it's put on conditional format button formulas 

[#2]

@kbloggs21 

To apply conditional formatting using Index Match in Excel, you can follow these steps:

1. Select the range of cells that you want to format in Table 1.
2. Go to the "Home" tab in the Excel ribbon.
3. Click on the "Conditional Formatting" button.
4. From the drop-down menu, select "New Rule."
5. In the "New Formatting Rule" dialog box, select "Use a formula to determine which cells to format."
6. In the "Format values where this formula is true" field, enter the following formula:

=INDEX(Table2, MATCH($A1, Table2[Employee ID], 0), MATCH(B$1, Table2[#Headers], 0))<>""

Note:

• Replace "Table2" with the actual range reference of your holiday table.
• Adjust the range references ($A1 and B$1) according to the location of the first cell in your selected range.

1. Click on the "Format" button to choose the formatting style for the cells that meet the condition.
2. Click "OK" to apply the conditional formatting rule.

This formula uses the INDEX and MATCH functions together. It looks up the Employee ID in Table 1 and matches it with the Employee ID column in Table 2. Then it matches the date in Table 1 with the column headers in Table 2. If there is a match and the corresponding cell in Table 2 is not empty, the conditional formatting will be applied to the cell in Table 1.

Make sure to adjust the range references and table names in the formula to match your specific setup. The text was created with the help of AI.

Hope this will help you.

[#3]

@NikolinoDE the formula unfortunately did not work 

[#4]

@kbloggs21 

Maybe you can consider using the IF function to combine the conditions and apply the conditional formatting. Here is an example of how the formula could be structured using the IF function:

=IF(AND(MATCH('Forecast Data (Current) - HOL'!$E161, 'Leave Chart - Weekly - Auto'!$A$1:$A$293, 0),

        MATCH('Forecast Data (Current) - HOL'!DF$8, Table_Leave_Chart___Weekly___Auto[[#Headers],[01/07/2023]:[30/12/2023]], 0)),

     INDEX('Leave Chart - Weekly - Auto'!$I$1:$AI$293,

           MATCH('Forecast Data (Current) - HOL'!$E161, 'Leave Chart - Weekly - Auto'!$A$1:$A$293, 0),

           MATCH('Forecast Data (Current) - HOL'!DF$8, Table_Leave_Chart___Weekly___Auto[[#Headers],[01/07/2023]:[30/12/2023]], 0))>8,

     FALSE)

This formula combines the two conditions using the AND function and returns TRUE if both conditions are met. Otherwise, it returns FALSE. You can apply conditional formatting based on the TRUE/FALSE values. Text and formula based on AI processing.

I hope these suggestions help you troubleshoot the issue with your conditional formatting formula.

[#5]

Thanks, the conditional format says enter a valid formula -

below is the formula -
IF( AND( MATCH($E145, 'Leave Chart - Weekly - Auto'!$A$2:$A$293, 0), MATCH('Forecast Data (Current)'!DB$8, Table_Leave_Chart___Weekly___Auto[[#Headers],[01/07/2023]:[30/12/2023]], 0), INDEX('Leave Chart - Weekly - Auto'!$A$2:$A$293, MATCH('Forecast Data (Current)'!$E145, 'Leave Chart - Weekly - Auto'!$A$2:$A$293, 0), MATCH('Forecast Data (Current)'!DB$8, Table_Leave_Chart___Weekly___Auto[[#Headers],[01/07/2023]:[30/12/2023]], 0)) ) > 8, TRUE, FALSE )

[#6]

Please provide detailed information.
Excel version, operating system, storage medium, etc.
A file (without sensitive data) would also be helpful.

[#7]

Hi, 



Excel version - Microsoft® Excel® for Microsoft 365 MSO Version 2208 Build 16.0.15601.20676 32-bit


operating system - Microsoft Windows


storage medium - it's stored in a folder. but would want to move it to sharepoint. 



Thanks

[#8]

@kbloggs21 

You need to use the INDIRECT function for the table call outs. The conditional formatting formula block won't recognize your table name as a table so you need to input it as INDIRECT("Table1[Column]"), where Table 1 is the table name, and Column is the table column you're referencing. The table and column need to be in quotations.