Conditional formating with multi lookup values

Seko_sonogo 

See the attached workbook.

According to this article, you need to use the INDIRECT function to be able to refer to an Excel table in a Conditional Formatting formula. Here is such a formula:

=IF( COUNTIFS( INDIRECT("Table1[emp ID]"), $AY$3, INDIRECT("Table1[start date]"), "<="&AX8, INDIRECT("Table1[end date]"), ">="&AX8) > 0, TRUE, FALSE )

(Most spaces are just for readability.  Strictly speaking, the IF function surrounding the COUNTIFS function is not necessary, because of how Excel converts numbers to Booleans, but it makes the intent clearer.)

Seko_sonogo 

Hello there. Conditional formatting applies formatting to a cell using true or false. I.e. if the condition is true for that cell, the formatting will be applied. To do this you will need to use a formula to determine the conditional formatting AND a formula to determine the dates for the conditional formatting.

Recall that in excel dates are just number values, such that Excel considers the date 2 Jan 2023 (44,928) to be a higher number than 1 Jan 2023 (44,927). So, whether any one date in your calendar table is conditionally formatted will depend on whether it is higher or lower than the approved leave dates. We will call these the "target dates".

If the date (2 Jan 2023) is greater than or equal to > = (1 Jan 2023) AND less than or equal to < = (3 Jan 2023), then it is TRUE that it is within the leave dates. Conditional formatting formulas only need to return true or false. So, (refer to my screenshots for cell refs), you can write the formula as follows:

=AND(A8>=$D$3,A8<=$E$3)

See figures 1 and 2. Where A8 is the first date in the table, D3 is their leave start date and E3 is the end date. That is, in order to be formatted, the date must be greater than or equal to the start date AND less than or equal to the end.

Notice the use of "$". Conditional formatting works as if you are copy pasting it from the cell you begin in. So, apply it to the area =$A$8:$G$12

Now our conditional format is able to format the table of dates depending on whether the date is within the leave period from D3 to E3.

To incorporate the emp ID number in B3 (your AY5) such that the formatting changes according to the ID you enter, we need to have something that changes the target dates of the conditional format. So, we put formulas into D3 and E3 that are dependant on the employee ID in B3. 

In D3 we put:

=XLOOKUP($B$3,K4:K6,L4:L6)

and in E3 we put:

=XLOOKUP($B$3,K4:K6,M4:M6)

See figures 3 and 4. These formulas look at B3 (your AY5) and looks up the date in the table (XLOOKUP is far better than VLOOKUP btw). So, when you change the employee ID number, this changes D3 and E3. Because D3 and E3 are the target dates for our conditional format, your conditional format will also update.

See how this works in figures 5 and 6.

Fig 1

Fig 1

Fig 2

Fig 2

Fig 3

Fig 3

Fig 4

Fig 4

Fig 5

Fig 5

Fig 6

Fig 6

Hope this helps!