Vlookup Across Two Columns And Sheets

[Melibau]

Hello!

I have two excel sheets where the emails are slightly different across the two sheets. I've created mock examples below and highlighted the difference:

Sheet One (also includes salary level) is the master sheet and the data doesn't change:

Sheet Two (Does not include salary level) is a weekly file that is pulled and the data can change from week to week:

Is there a tool that can search by both the first and last name and output the salary level into sheet two? 

I tried using a Find and Replace tool, but it only allows me to choose one column, which doesn't output the salary levels correctly if someone shares the same first or last name. I need to keep the first and last name separate or I think it would work better than the email. The Union and Join tool don't seem to be correct either (but I just might not be doing it correctly). It works in Excel, but still trying to understand the advanced Vlookup skills in Alteryx.

Thanks in advance!

[BrandonB]

Can you use a join tool for the two datasets and pick the first name and last name as columns to be joined on?

[BrandonB · Accepted solution]

 

[Melibau]

Thanks Brandon for the idea. I had tried that but it only joined a couple (eg 5 entries were joined of the 55 that should have been joined). In Excel, my vlookup is able to join almost everything (eg 52 entries of the total 55 entries brought a salary level result).

[PhilippK]

Hi @Melibau ,

maybe the names are written differently in data set 1 & 2?

I recommend that you join both data sets via the email field instead.

Does this produce the desired outcome?

If not - I'll have a look if you send me the pseudonymized data.

Best regards

Phil

[Melibau]

Actually I tried it again, but I did some data cleansing as the master file was all in Caps. That seemed to do the trick. Thanks!