Overlay a column based on text in any of several columns

[Rob48]

Hi, I need to overlay the existing data in a column with new data based on whether the text in any cell of several other columns matches a list from a separate data set.  I've tried several approaches but I can't quite get it to work without getting too unwieldy and i'm sure there's a more elegant solution.

As an example, here's a list of people who's profession is known to be jugglers:

List of Jugglers

Smith

Jones

Brown

Moore

Johnson

Miller

Taylor

My main data looks like the below.  If any of the people in the list of jugglers show up in the "Names" columns 1-5 then i need to overlay the data in the "Profession" column with "Juggler"

Name 1	Name 2	Name 3	Name 4	Name 5	Profession	User List	Professing w/Overlay
other	other	other	Moore	other	Doctor	User 1	Juggler
other	other	other	other	other	Lawyer	User 2	Lawyer
other	other	Smith	other	other	Doctor	User 3	Juggler
other	other	other	other	other	Fireman	User 4	Fireman
Johnson	other	other	other	other	Accountant	User 5	Juggler
other	other	other	other	other	Fireman	User 6	Fireman
other	other	other	other	Miller	Policeman	User 7	Juggler
other	other	other	other	other	Lawyer	User 8	Lawyer
other	other	other	other	other	Musician	User 9	Musician
other	other	other	other	other	Fireman	User 10	Fireman
Taylor	other	other	other	other	Painter	User 11	Juggler
other	other	other	other	other	Painter	User 12	Painter
other	other	other	other	other	Accountant	User 13	Accountant

[binuacs]

@Rob48 One way of doing this

[OllieClarke]

Hey @Rob48 

Here's one way of doing this:

I've used Find and Replace tools as they're more performant than Join tools when matching on a single string field.

There are other ways which don't involve transposing the full data, but they are more complicated to configure. Let us know if that's a consideration.

Ollie

[jdminton]

Here's a way that takes a few tools, but is pretty simple and dynamic.

  

[ScottLewis]

Simplest version I could come up with. As should be clear from the other examples, there are a lot of ways to do this, depending on what tools you're comfortable with, how robust you need to be and whether you need to keep the original profession as well as the overlay. 

Generally, you can split tasks like this into two steps. First, flagging the rows with some characteristic. Second, taking some action on that subset and either no action or a different action on the remainder of the original set. The second task often takes the form of join (original set to subset) followed by formulas on the left/join outputs and a union to put the original set back together.