Display all results of my vlookup

[rchua]

Hello. Good day everyone. Can I get advice on how I can get the results below. I tried find and replace but I only get 1 result. I used join tool as well but I wanted to show the result for DEF to be blank. Is there any way this is possible? Also, if there is a way to get the highest value in Column 5 on the Output table and being reduced back to the original Table 1. Thank you in advance! Would really appreciate anybody looking into this :)

Table 1

Column1	Column2
ABC	123
DEF	456
GHI	789

Table 2

Column1	Column2	Column3
ABC	123	5
ABC	159	6
ABC	753	12
ABC	456	3
ABC	135	4
GHI	147	7
GHI	123	8
GHI	159	9
GHI	478	10
GHI	236	45

Output

Column1	Column2	Column3	Column4	Column5
ABC	123	ABC	123	5
ABC	123	ABC	159	6
ABC	123	ABC	753	12
ABC	123	ABC	456	3
ABC	123	ABC	135	4
DEF	456
GHI	789	GHI	147	7
GHI	789	GHI	123	8
GHI	789	GHI	159	9
GHI	789	GHI	478	10
GHI	789	GHI	236	45

Highest value in Column 5

Column1	Column2	Column3	Column4	Column5
ABC	123	ABC	753	12
DEF	456
GHI	789	GHI	236	45

[alexnajm]

Use a Summarize tool on Table 2, grouping by Column 1 and 2 and getting the Max of Column 3. That can go into the Join then!

If you want to bring back the records that drop out, use a Union tool after your Join to bring them back.

[alexnajm]

Alternatively, you could use the Find Replace: VLookUps with Designer - Alteryx Community

[MelGibson · Accepted solution]

Hope this helps - - input both tables - add a join (join column 1) in the join tool - rename right column 1 to Column 3, right column 2 to Column 4, right column 3 to Column 5 - add a union tool and union join and left output (left over from table 1) - add a sort tool Column 1 ascending and Column 5 descending - add a sample tool select first N rows  with N = 1 and group by Column 1

[binuacs]

@rchua another option