Forum Discussion

Copper Contributor

Jun 29, 2023

Index Match Conditional formatting

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 ...

Formulas and Functions

kbloggs21

Copper Contributor

Jun 29, 2023

NikolinoDE the formula unfortunately did not work

NikolinoDE

Gold Contributor

Jun 29, 2023

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.

kbloggs21
Copper Contributor
Jun 29, 2023
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 )
- NikolinoDE
  Gold Contributor
  Jun 29, 2023
  Please provide detailed information.
  Excel version, operating system, storage medium, etc.
  A file (without sensitive data) would also be helpful.
  - kbloggs21
    Copper Contributor
    Jun 29, 2023
    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