Multiple columns to look up

Hi I'm new to alteryx

So I have 20 columns named Phone 1, Phone 2, Phone 3 etc. and I have one lookup table Phone Number. I need to compare all the phone numbers in Phone 1 column with Phone Number table to see if there is a match. This process goes on for all the 20 columns. I'm wondering if there is an efficient way to do it instead of joining 20 columns with the lookup table one by one.

I really appreciate if you guys can help 🙂

Book1.xlsx

Help

Join

Accepted answers

T_Willins

Hi @hyh,

Thank you for providing sample data and a desired output. It makes it much easier. While there is more than one way to do this, I started with a RecordID tool so the results could be matched to original data. I then used a Transpose tool to put all the phone numbers into a single column, then Joined with the lookup table to get the matches; Cross Tab to bring the data back to the original format, Join back to the original data (using the record ID), then Union in any data that did not have any matches. Finally, Sort by record ID to put everything in the right order.

Lookkup Multiple Columns.png

Lookup Multiple columns.yxmd

All comments

T_Willins

Hi @hyh,

Lookkup Multiple Columns.png

Lookup Multiple columns.yxmd

hyh

Hi @T_Willins,

Thank you for your reply. Before I try your method, a quick question, there are other info in my actual file like names, street addresses etc. Will that affect your workflow? Also Match Phone 2 should be Match Phone 3 right? Thanks!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703