VLOOKUP help

[lancegoh1]

Hello

I'm trying to use the "join" tool to do a vlookup from input 2 and input 1.

under the join tool, for the Left option I put "F5" and then for the Right option I put "a/c number".

I then put a union tool and joined all 3 lines from the join output into the union tool.

when I place the browse tool after the union tool, all my cells appeared [Null], basically everything was blank.

can someone guide me as how do I do a vlookup and create an additional column as my desired output ?

Input 1:

F5

123456

654321

123123

321321

333333

Input 2:

a/c number	Name of Reviewer
123345	John
333333	mary
321321	james

desired Output:

F5	Name of Reviewer
123456	John
654321
123123
321321	James
333333	mary

[cmcclellan · Accepted solution]

You're after a left outer join, so union only the left and join outputs together.

The top stream is simple and doesn't care about the order of the records

The bottom stream does the same thing (essentially) but creates a Record ID so you can then put the records back in the same order (because the UNION will put all the lefts first and then the joins)

[cpituley]

Hi Lancegoh1,

I would use the find and replace tool to append here. Give it a shot and let me know how it works for you. Closest exact tool to a VLOOKUP in Excel.

Best,

Cameron

[ponraj]

You can use multi join tool to get the desired results. 

[lancegoh1]

hi Cmcclellan thanks for your advice. everything seems to be working perfectly, but when I did a total count, i noticed that the total count after I did a union was greater than my original count. wondering why is that so?

Regards,

Lance

[MarqueeCrew · Accepted solution]

@lancegoh1,

Here's the thing about a join, for every join key match in the L & R inputs you get the product of L times R records on output.  Whatever your join key is in the vlookup, if you put a UNIQUE tool after your WIP consolidated 4th July.xlsx with that key checked for duplicates, you will find that the count is now 4,232.

The vlookup finds the 1st occurrence of the lookup value if multiple are found.  The UNIQUE tool outputs the first occurrence as well.  If there is a preferred record to match, you should SORT the data prior to the UNIQUE tool to put that record first.

Cheers,

Mark