How should I do this (function or Visual basic)

[Pasi]

I have one workbook with two different worksheets

In other worksheet there is information what is old cost centre and what is
correspondin new cost centre and new name of that cost centre (cc):

old cc new cc new cc name
123 222 management
124 333 service
125 444 etc... phone costs

In other worksheet there is:

phonenumber old cc
0401234 123
0402345 124
0501234 125


And I need to get new cost centre number based on corresbonding cost centre
numbers in other worksheet and result is like this:

search term result of search term
phone number old cc new cc name of new cc
0401234 123 222 management
0402345 124 333 service
0501234 125 444 etc phone costs

How should I solve this thing? Is there a function to do that or do I need
visual basic. And plz I need a very strict answer about this...

Thx in advance and sorry my poor english!

 

[Pasi]

And one minor thing... there is at least 100 different cost centre numbers...
which means, I don't like to write them all... just automaticly find
corresbonding number and name in the right place...

 

[Pete_UK]

Use VLOOKUP, like this:

C2: =VLOOKUP(B2,other_sheet!A:C,2,0)
D2: =VLOOKUP(B2,other_sheet!A:C,3,0)

Put your sheet name in for other_sheet - use apostrophes around the
sheet name if it contains spaces.

Copy down as far as required.

Hope this helps.

Pete

 

[muddan madhu]

sheet 1 has
old cc new cc new cc name
123 222 management
124 333 service
125 444 etc... phone costs


sheet 2 has
phonenumber old cc
0401234 123
0402345 124
0501234 125

sheet 3 - you have phone number in col A
in cell B2 =VLOOKUP(A2,sheet2!$A$1:$B$4,2,0) and drag it down
in cell C2 =VLOOKUP(B2,sheet1!$A$1:$C$4,2,0) and drag it down
in cell D2 =VLOOKUP(B2,sheet1!$A$1:$C$4,3,0) and drag it down

change the range ranges according to your need.

 

[Pasi]

I tried these both but these did not work (because of me, think so)...

Again:

sheet2

A1=old cc, B1= new cc, C1= new cc name (titles)
A2= old cc number and A3 and so on
B2= new cc number and so on
C2= new cc name and so on

sheet2
A1=phonenumber, B1=old cc (titles)
A2= phonenumber and so on
B2=old cc and so on...

sheet3
A1=phonenumbers (title), B1= old cc number (i think), C1=new cc, D1=new cc
name
A2 and so on are phonenumbers

and...

if I try function VLOOKUP in the cell B2 (VHAKU is finnish version on
VLOOKUP): =VHAKU(A2;sheet2!A1:B4;2;0) it gives me answer MISSING?

And also in finnish version of Excel 2007 I have to use ; separation
character.

 

[Pasi]

Oops... it is working... this is really stupid when MS make finnish
translations to functions (and english functions don't work). Now it works...
because vlookup is PHAKU not VHAKU... stupid me..

paha