Vlookup - one value to lookup for two columns to return - only inner join returned

[Baz123]

Hi,

I have been trying to resolve a VLOOKUP problem. In excel I have two files, that both have a common field of PO Number.

Usually the main data set has NULL in the purchaser name and location. I use the second reference file to lookup the PO and on each column perform a VLOOKUP to return the name, and the another lookup to return the location.

Data set (main file): 

Supplier	Purchaser	PO Number	Location
A	NULL	20714	NULL
A	Bob S	21677	BBBB
B	NULL	21655	NULL
B	NULL	21655	NULL
C	Mary Y	21341	EEEE

Reference file:

Supplier	Purchaser	PO Number	Location
A	Jim K	20714	AAAA
B	Sam K	21655	CCCC
B	Sam K	21655	CCCC

Required output:

Supplier	Purchaser	PO Number	Location
A	Jim K	20714	AAAA
A	Bob S	21677	BBBB
B	Sam K	21655	CCCC
B	Sam K	21655	CCCC
C	Mary Y	21341	EEEE

In the real data sets I am using a join tool with the main data going in the left (166 rows), the reference table going in the right (1094 rows). 

Join tool is configured so that they are joined on PO Number. The main columns (left) are all selected - except the two in question: Purchaser and Location. The right reference has all deselected except for Purchaser and Location.

My issue is all I am getting 1660 in the inner join return???

All I want is the left join to return the required output with 166 rows.

What am I doing wrong? Should I be using the Find replace tool instead?

Thanks.

[FilipR]

Here's one way of doing this.

[binuacs · Accepted solution]

@Baz123 Make sure your reference file has no duplicated records which are giving you the unexpected result

[Baz123]

Thanks @binuacs - it was duplicates causing errors in the reference file.

Once I added the unique tool as you had it, then it worked.

:)