multiple lookup to return a result

Hi,
I'm new to the forum, but a big shout-out to the contributors sharing their vast knowledge. It is very helpful.

I want to return a value "PH" for public holiday in the calendar I have created. Sheet 1 (AUG) has the State the employee belongs to and the Employees name and the subsequent days of the month. On Sheet 2 (HOLIDAYS) I have the list of public holidays by State, date and description.

On Sheet 1(AUG) I want to have it automatically look at the Date AND State the employee is in and return "PH" if it matches within the holidays listed on Sheet 2 (HOLIDAYS).

I have been looking at Vlookup and MATCH but getting myself totally confused.

I have attached a sample spreadsheet

thanks in advance for any assistance.

cheers

Kudu Rider

You could have an INDEX/MATCH formula in each cell of your calendar which could return PH to the cell if there is a match on the state and the date.

However, you have data validation set up in those cells which implies that you also want to enter data manually into the cells (e.g. L ), and if you do this it will overwrite the formula that you have set up.

I think you will need to reconsider how you are going to record the data - maybe by keeping a list in the Holidays sheet of every leave-booking and then to fill the calendar cells from that table, rather than do it manually.

Hope this helps.

Pete

Something like this in C4 will return "PH" where relevant, copied across and down.

=IFERROR(INDEX(HOLIDAYS!$D$2:$D$6,MATCH(1,(DATE($AF$1,MONTH($AC$1&0),C$3)=HOLIDAYS!$B$2:$B$6)*(HOLIDAYS!$A$2:$A$6=$A4),0)),"")

I'm not sure how you want this to sit alongside cells with "L" in though.

thanks for the quick responses. I'll give the above formulas a go and will let you know how I go.

Or try this

=IF(COUNTIFS(HOLIDAYS!$A$2:$A$6,$A4,HOLIDAYS!$B$2:$B$6,C$2)>0,"PH","")