Need a formula to update data in 2 columns

[MMcGee]

Hello:

I have a spreadsheet with 2 columns that consistently need to be updated.
One column is a contact person ("Contact"), and another column is a group
that the contract person is responsible for ("Group"). I download the data
from SAP once a week and the contacts and groups in SAP never match
correctly, so I have to manually change them. I do this by filtering on the
'Group' column and changing the name for each group. I would like to create
a formula to do this automatically.

My dilemma is, not all of the groups and contacts are a mismatch. Out of
the 14 choices in the "Group" column, only 7 need to be updated with the
correct contact person. 3 of those 7 groups have the same contact person.
The closest I have come is an 'IF' function, but that doesn't work because it
does not apply to all the groups. Any suggestions? I'm working in Excel
2003. Thanks!

 

[Pete_UK]

You should be able to use vlookup to "translate" one group code to
another. List all your 14 groups say in X1:X14 and next to them put in
what you want them to be in Y1:Y14. Then, assuming the groups from SAP
are in A1, use this formula:

=VLOOKUP(A1,X$1:Y$14,2,0)

Copy this down as required.

Hope this helps.

Pete