Highlighting a single cell when partial text shows up in a range of other cells

Question

I work for a physician office and we keep track of which patients are ready in a room and which doctor goes in to see that patient. We use a google spreadsheet to track this information. I want to have a cell at the top of the spreadsheet labeled for each room 1-10 highlight(green) when a patient is in a room, and again highlight a different color(purple) if doc is in a room.

We record a patient in a room next to the pt name on spreadsheet with the letter "r" (for ready) with the room number next to it. For example "r1" designates that a patient is ready in room 1.

We designate that a doc is in the room by changing the "r" to "i" (for in) with the doc's initials. For example "i1jd" would mean Dr. John Doe is in room 1 with the patient.

We also use "d" next to a room number to designate that the patient and doctor are done and no longer in the room, for empty rooms the room number at top of page may remain white.

We have up to 5 doctors per day with a column for each doctor's scheduled patients. This would be helpful to keep the room numbers locked at the top of the screen so that at a glance we can see which rooms are occupied and which are empty without walking down the hall to see each room.

I have tried this formula:
=VLOOKUP(C1,INDIRECT("c2:c1000"),1,FALSE)=A1
but I can only get it to look for the text in C1 and can't get it to look for a partial match such as i1jd.
I found that formula on a different forum.

Here is a link to an example spreadsheet: https://docs.google.com/spreadsheets/d/1NS6iJGvLsxPS9G0oHzHed55uLXt2YClgpTWIJWVqDaE/edit?usp=sharing

Answer 1

Here's a link to the copy I made. https://docs.google.com/spreadsheets/d/1c0cDUDmSWtrsPXs8eYMeREJsrx9nTdbG9UZwRi-e0_Y/edit?usp=sharing

So to start, I have at A1 a concatenated version of all the room codes by using TEXTJOIN(=TEXTJOIN("",TRUE,{B3:B12,G3:G12,L3:L12})). I decided to just not have spaces 'cause why not. The {B3:B12,G3:G12,L3:L12} is an array that combines together all the cells that could have codes written in them. The true flag tells the function to ignore blank spaces, though it doesn't matter much with what I'm doing.

Next, the conditional formatting. I simply used =REGEXMATCH($A$1,"r"&REPLACE(C1,1,2,"")). Regex can be complicated, but in this situation, this is best, I think anyways. The string being searched is the A1, dollar signs for absolute value, meaning it won't move relative to a cell. Next the actual regex pattern. There is actually two conditional formats, one for 'r' and 'i'. "r"&REPLACE(C1,1,2,"") puts the string 'r' and a modified string. It takes the value of C1 and removes the first two characters. And since it isn't absolute it will change value when applied to other cells. Do the same for i and you're done.