Tool that acts like excels VLOOKUP

[rtlane]

Excuse my newbie question. I'm trying to use the join tool similar to using vlookup in excel. The only issue I see is that if the data does not match, it removes the rows that do not match between the 2 files for the J output.

Example:

If I have the 2 inputs going into a join tool, it will output the matching 11 rows and not the rest.

Input 1

Input 2

Instead of this output:

I would want this output:

How can I do this?

[Kenda · Accepted solution]

Hey @rtlane! Use a Union tool after your Join on the L and/or R outputs. This should append the rows of data that were not joined.

[rtlane]

Wow thank you so much for the quick response. I knew it had to be something simple. lol 

[JefBus]

if i'm not mistaken, you can also lookup the price values based on ID using the find replace tool,

Oh, here it is:

• Match values in a VLookUp table to values in an existing dataset

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