VLOOKUP Single Table

[jai_cotman]

I have a single table with a list of employees, their direct manager and then the hierarchy up to the SVP level.  The employees field does contain the managers as well.  Each employee has an email address field.  What I'm trying to do is compare 2 columns for like values and if there is a match, return that employees email address.  

employee name	mgr name	svp	level 1	level 2	employee email	mgr email
employee 1	mgr 1	big boss	leader 1	mgr 1	emp1 email address	null
mgr 1	mgr 2	big boss	leader 1	mgr 2	mgr1 email address	mgr1 email address

I've tried to use the formula tool to search the columns to find matches, but the comparison seems to be happening at the row level and I need to compare the manager and employee columns and if there is a match (employee name = manager name), then I need to return the employee email address in a new column like mgr email.  Is there a way to do this like a VLOOKUP in Excel?  Thanks in advance.

Jai

[apathetichell]

Join tool (although find/replace can be used as well) is my usual pick. Self-join and compare the two fields as you described.

[jai_cotman]

I've attached some sample data that I hope better visualizes what I'm working with.  When I do a VLOOKUP in Excel, I can return the managers email address for each instance of the managers name.  Is there a way I can do that in Alteryx?

[apathetichell]

Hi!

Quick question - do you want all managers addresses or just the direct manager - or all the mangers/svps/etc.

If it's the former - see the attached.

for the later it's totally doable - but there's a transpose and then join base.

[atcodedog05 · Accepted solution]

Hi @jai_cotman 

In addition to @apathetichell method of self join using join tool. Here one more way to do it.

You can use Find & Replace tool which works exactly like vlookup. Here below i am trying to find Mgr Name in Employee Name and fetch email Id. Find & Replace is slightly more safer because it prevents many to many join (leading to data explosion)

Here is some resources to get an overview of the tools

Find & Replace tool : https://community.alteryx.com/t5/Interactive-Lessons/VLookUps-with-Designer/ta-p/80201 

Join Tool : https://community.alteryx.com/t5/Interactive-Lessons/Joining-Data/ta-p/76634 

Hope this helps 🙂

[jai_cotman]

This is exactly what I was trying to do!  Thank you all for your assistance!