Optimize multiple vlookups

Question

Hello community,

I have a dataset with 28 fields and half a million rows.
Within my dataset, I have 6 fields which I need to lookup in another table (simple lookup to get another field).

I can solve this using a couple of different ways:

* Combination of transpose and cross-tabs (will be an expensive operation for 28 fields and half a million rows)
* Use 6 'Join' tools (This is what I did for the attached sample workflow)
* Use 6 'Find and replace' tools

Question - Is there a tool in Alteryx which will do the lookup in a better/optimized way?

In my example - Field 1, 5, 10, 15, 20, 25 contain names which I lookup to find emails from the second table.

I found a post from 2019 with the same use case but with no solution.

multiple_vlookups.yxmd

Qiu · Answer

@amadra 
I have a feeling maybe an Iterative Macro should work, but need more time to check 😁

phottovy · Answer

@amadra

Gotcha, now I see where the transpose comes in. Here is a slightly modified version.

multiple_vlookups.yxmd

amadra · Answer

@phottovy  - thanks for creating a potential solution.
Your solution gives one column for email after summarize tool, but I'll need emails (after lookup) for Field1, 5,10,15,20 and 25.

Let's see what others have to say.