IF/OR The Result Of An Index Match Statement

[wcso105]

I have a code to search a monthly schedule for only my shifts and display where I am working during the month. Some of these shifts will show a location and some will show only a letter from HOLIDAY. I have a second code to search for these letters and display the result HOLIDAY and I would like to combine the two codes so HOLIDAY will be displayed in my shift calendar.

A cell code to show my job for a specific day is in H10: =IFERROR(INDEX(C54:C69,MATCH($I1,$A54:$A69,0)),"") C54:C69 is where work locations are shown, I1 is a drop-down to select the employee name to show the calendar for, A54:A69 is where the employee roster is

The code to show HOLIDAY is =IF(OR(H10="H","O","L","I","D","A","Y"),"HOLIDAY",(INDEX(C54:C69,MATCH($I1,$A54:$A69,0)))) and is currently in another cell. I want to combine these two codes so the result in H10 will show the location I am working or HOLIDAY. Currently it shows the location I am working or whatever letter from HOLIDAY is in H10 after running the first code.

The code shown is for a single cell which I can extend to cover the entire calendar result if I can get it for one cell.

 

[AliGW]

Which version of Excel?

 

[AliGW]

For example, if you have Excel 2021 or newer, this in H10:

=LET(x,INDEX(C54:C69,MATCH($I1,$A54:$A69,0)),
IF(ISNUMBER(MATCH(x,{"H","O","L","I","D","A","Y"},0)),"HOLIDAY",x))

 

[wcso105]

For example, if you have Excel 2021 or newer, this in H10:

=LET(x,INDEX(C54:C69,MATCH($I1,$A54:$A69,0)),
IF(ISNUMBER(MATCH(x,{"H","O","L","I","D","A","Y"},0)),"HOLIDAY",x))

I have Excel 2021 and the code works perfectly. I am not as yet familiar with the coding you used but will study it now for future use. Thank you very much for your assistance.

 

[AliGW]

It's a formula, not actually code. If you want it explaining, just shout.

You're welcome - feel free to hit LIKE under my post.