Join with 10 different columns as options

Ponycatsurvivor007

Hi everyone,

As the title says, I want to perform a join on two data sources - specifically, on the field "country". However, in data source A, the country name isn't standardized: there are 10 different options for how it could be spelled/written. So, I have a mapping table in data source B, where I have 10 columns, one per option. This is how I created the workflow:

Is there a more elegant (& efficient) way to handle this problem other than trying 10 different joins?

Thanks in advance for your help!

Actual-list.xlsx

All-Options.xlsx

Country-Join.yxmd

OllieClarke

Hey @Ponycatsurvivor007 

Here's how I'd do this:

I guess by grouping by both recordID and country, it's possible that multiple matches could occur, but I would be surprised by this outcome. Just wanted to flag that...

Ollie

countries.yxmd