I could use some help creating a formula that refers to cell A1 and produces a result in B1 based on a search function that identifies a variety of potential the keywords that may appear in A1. For example; column A will be devoted to hospital room names that may come in a variety of forms, but mean the same thing (e.g., Operating Room #1, O.R. #1, OR #1, Surgery #1, etc.), and depending on the room type (operating room vs. emergency room vs. ICU room, patient room, etc.), I would get a different result in column B. I know that I could create a bunch of cells worth of search formulas, but this could take up to 100 individual cells and I would like to condense this into one cell if possible. Would appreciate any direction. Thanks.
Is it possible to create a table of all possible entries and then in adjacent column the name you want to associate the them.
Then you can use a simple VLOOKUP() formula to match them up.