Correlation of data

[Guest]

I have 2 worksheets. Worksheet 1 has a list of all outstanding sales orders
containing the following, customer purchase order no, works order no.
Worksheet 2 has the same information for but from our old system.

The customer purchase orders will match on both systems but the works order
numbers will not.

What I am wanting to do is to corrolate works orders from the old system to
works orders from the new one using the customer purchase order as the search.

I have tried using VLOOKUP but this will only result the 1st finding.

Is there any other way to correlate this data.

Thanks

Regards

 

[Guest]

Let's assume you have customer purchase order no in column A and Works Order
No in column B in worksheet 1.

In worksheet 2, the same thing. In worksheet 2, sort the customer purchase
no in ascending order. This is a requirement for vlookup. Then, select the
entire range of data (highlight all of your data). Do not select the header
row. In the name box, in the upper left of your window, you should be able
to type the name of the range. Alternatively, go to Insert ->name-> define.
Call the named range OLDDATA

In worksheet 1, column C, type VLOOKUP(A2, OLDDATA, 2, 0)

The 2 specifies the second column in the olddata range, which is the works
order no from your old system.

Then, highlight the cell and drag it down.