Index Match Conditional formatting

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.