How to Input the Vacation List into the Employee Calendar

Dear all,
Could anyone help me with this?

Background:
I have 2 excel sheets:
Sheet 1 named "working schedule": include name of the employees, date of the month and type of shifts in the month (like L shift, D shift, B shift, etc)
Capture2.PNG
Date of the month
Name 1 2 3 4 5 6 7
Aby L L L L L L
Nani D D D D D D
Nancy B B B B B B
Hogie D D D D D


Sheet 2, named "Vacation tracking," includes all the holidays that employees have taken or are planning to take:

Name date of the month Type of holiday
Aby 5 Holiday
Aby 7 Holiday
Nani 1 Sick
Nancy 7 Personal leave
Nancy 2 Holiday

Capture3.PNG

The result that I want is to replace the type of holidays (in the sheet "Vacation tracking") into the working schedule (in the sheet "Working schedule") for the dates that employees have.
Expected result as below:

Date of the month
Name 1 2 3 4 5 6 7
Aby L L L Holiday L Holiday
Nani Sick D D D D D
Nancy B B B B B Personal Leave
Hogie Holiday D D D

Capture4.PNG

Could you help advise how to do it quickly instead of replacing one by one?
Because I need to work on replacing for the whole month with a list of many people.
Many thanks !!!

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

1. Suggest converting the ranges of data on the working schedule and vacation tracking sheets into tables.
2. Use the following formula to fill the expected result table as modeled in rows 11:15

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Please try

B3
=IFERROR(INDEX('Vacation tracking'!$C$1:$C$6,SUMPRODUCT(MATCH(1,--('Vacation tracking'!$A$1:$A$6=$A3)*--('Vacation tracking'!$B$1:$B$6=B$2),0))),
IF(INDEX('working schedule'!3:3,1,B$2+1)<>0,INDEX('working schedule'!3:3,1,B$2+1),""))

Regards.