Changing cells that previously required exact match, but now it is enough if a cell just contains the text
Note: I am looking for solutions that are compatible with Excel 2007.
I have the following setup:
Entry cell for region: V36. (Example text entered: Hawaii)
| Row | Name (Column B) | Region (Column C) | .... | Region (Column V) |
|---|---|---|---|---|
| 39 | Zeke | Hawaii | * Yes | |
| 40 | Dixie | Okinawa | * | |
| 41 | Hopper | Hawaii | * Yes |
The Names (B) and Regions (C) are fixed data.
The entry cell for regions (V36) is a cell where I can manually enter a region's exact name (such as Hawaii or Okinawa).
The second Region (V) column checks if the person in each row matches what's in the entry cell. The column cells contain the following formula:
=IF( AND(V$36=$C39; NOT(V$36="") );"Yes";"")
Basically, if the regions match and the entry cell is not empty, Yes is displayed. Otherwise, the cell is empty.
-----
Now comes the complication: It was introduced that one person can belong to two regions.
I see multiple ways to change the raw data:
- Change the Region (C) column to list multiple options. ("<A> and <B>")
- Introduce a "Region 2" column between columns C and D.
(I lean towards option #1 in case an idea comes in the future that people can also belong to more than 2 regions. But for now, let's assume that the maximum region is 2 for everyone.)
-----
QUESTION: How does the formula for the second Region (V) column change with each option?
The original formula: =IF( AND(V$36=$C39; NOT(V$36="") );"Yes";"")
The two options:
- Change the Region (C) column to list multiple options. ("<A> and <B>")
- Introduce a "Region 2" column between columns C and D.
Edit 1 (2026.07.04): Removed irrelevant data.
Edit 2 (2026.07.04): Excel 2007 limitation.