Mapping data from an old accounting system into a new system

Hi Mark, I think I have a solution for you. However, you only gave me the corresponding Oracle Acc and CC codes for these Sun codes:

10000 FIN
20000 OPS
30000 ITS

So the rest will need to be included for this to work on all your data.

First, create a second table. Column 1 will combine the Sun Account Code and cost centre using concatenation (i.e =A2 & " " & B2), second and third columns contain the corresponding Oracle codes.

Sun Combined Oracle Account Code Oracle Cost Centre
10000 FIN 100 10
20000 OPS 200 20
30000 ITS 300 30

Once you have this table, you can use it as the table array for a VLookup. Return to your original data table (with the Sun Account code and Sun Cost codes and empty Oracle columns) and use Vlookups to populate the Oracle columns. Your lookup value should be concatenate the Sun codes and your table array should be the table we created above:

=VLOOKUP(A2& " " & B2,Oracle!$A$7:$C$9,2, FALSE)

Remember to alter the column index value to bring in the Oracle information into the right column.

This worked on the spreadsheet you left me, but have a play with the formulae and let me know how you get on. The key is creating that first table with the combined Sun values and then using them in a Vlookup to bring in the Oracle data. Good luck!

All the best,

Anthony