I have two excel files; the original file contains 10K patient names and medical condition, the goal is to identify patients (about 400 ) with special conditions so that the mail that gets sent to them is different than the rest of the list.

Original File Template:

Mailing Template:

In the Mailing Template, I want to classify the Type of Mail based on the diagnosis. Common diagnosis would be "LV1" and anything that I would identify as a special diagnosis, like cancer or HIV, would be "LV2"

My initial approach would be to filter the Original File by the special diagnosis and then use a True/False condition of that filtered list against the Mailing template and manually flag LV1 or LV2. But is there a method or formula that could scan the Original File to look for the keywords (eg cancer and HIV) and automatically assign the corresponding names in the Mailing List with "LV1" or "LV2"?

CodePudding user response：

I believe if you can cover all the cases you're interested in, it's possible with a IF(OR) statement, for example:

Let's say B5 is the cell with the diagnosis, in your target cell (where you want "LV1" or "LV2" to appear) you will write the next formula:

=IF(OR(B5="Common*", B5="Broken*"), "LV1", "LV2")

Note the "*" in the diagnosis condition text, it will allow any cell that begins with such text to be considered true. For example, "Common*" will consider both "Common Cold" and "Common Fever" as "LV1" cases.

This solution may be problematic if you have a lot of different diagnoses to cover.

CodePudding user response：

Exact Matches

If you expect to add additional condition/mailing types down the road, =XLOOKUP() would be a good option.

• In column D this would match the diagnosis to a set of values in column F, and return the value in column G.
• You can add as many diagnosis/mailing type values as you need without changing formulas.

In cell D2: =XLOOKUP(C2,F:F,G:G):

|   | A         | B          | C           | D                    | E | F           | G     |
|--- ----------- ------------ ------------- ---------------------- --- ------------- -------|
| 1 | Last Name | First Name | Diagnosis   | Type of Mail         |   | Match       | Index |
| 2 | Doe       | John       | Cancer      | =XLOOKUP(C2,F:F,G:G) |   | Cancer      | LV2   |
| 3 | Smith     | John       | HIV         | LV2                  |   | HIV         | LV2   |
| 4 | Smith     | Jayne      | Broken Arm  | LV1                  |   | Broken Arm  | LV1   |
| 5 | Rock      | Dwayne     | Common Cold | LV1                  |   | Common Cold | LV1   |
| 6 | Foster    | Jane       | Common Cold | LV1                  |   |             |       |

Note =XLOOKUP() uses the same concept as using =INDEX(G:G, MATCH(C2, F:F, 0)) in previous versions of excel (and produces identical results).

Wildcard Matching

• To support using keywords, you would then need to set the [match_mode] argument in =XLOOKUP() equal to 2, which adds the ability to use wildcards (eg * and ?).
• The following would match any diagnosis where the first word matches any first wordcommon using common*.

In cell D2: =XLOOKUP(LEFT(C2, IFERROR(SEARCH(" ", C2)-1, LEN(C2)))&"*",F:F,G:G,0,2)

|   | A         | B          | C               | D                                                                       | E | F          | G              |
|--- ----------- ------------ ----------------- ------------------------------------------------------------------------- --- ------------ ----------------|
| 1 | Last Name | First Name | Diagnosis       | Type of Mail                                                            |   | Match      | Index          |
| 2 | Doe       | John       | Cancer          | =XLOOKUP(LEFT(C2, IFERROR(SEARCH(" ", C2)-1, LEN(C2)))&"*",F:F,G:G,0,2) |   | Cancer     | LV2            |
| 3 | Smith     | John       | HIV             | LV2                                                                     |   | HIV        | LV2            |
| 4 | Smith     | Jayne      | Broken Arm      | LV1                                                                     |   | Broken Arm | LV1            |
| 5 | Rock      | Dwayne     | Common Cold     | Matches Common                                                          |   | Common     | Matches Common |
| 6 | Foster    | Jane       | Common Anything | Matches Common                                                          |   |            |                |

You would need to adjust some in the event there is crossover in keywords or to search for multi-word keyword, but this should be a good place to start.