Alteryx Workflow - Vlookup differing values

Question

Hi all,

I am stuck with a situation whereby i would need to vlookup a value from excel A and get another value from excel B. I tried using the find and replace tool but was only able to get a null response.  Example would be that in excel A "Tan Amos" and "Lee Jane", however in excel B would be "Chan Amos" and "Tan Jane". Would it be possible to create a column to show the results?

Excel A

Excel B

Results

Thank you!

Excel A.csv

Excel B.csv

Christina_H · Answer

@atcodedog05 yes, I didn't think of that one, but between us we've provided a range of options!

@gavinloi either of our solutions works on your test data. Which one is best for you depends on your full data. If there could be both duplicates and missing names you'll need a join tool with s union after it.

atcodedog05 · Answer

Hi @Christina_H

Actually I had a different thought on this what if a name has multiple surnames Find & Replace like vlookup always takes only the first occurrence. Just had a different thought so added a possibility 😅

Christina_H · Answer

@atcodedog05 I'm sure there was one earlier where you used find and replace and I used join!  There's really never only one solution.  I chose find and replace this time to avoid having to use a union if there are non-matching values.