match number to name

[ash.gary]

I have a spreadsheet that contains three worksheets.

The 1st worksheet has a list of account codes only. The account code
is a combination of a department number and account number which are
separated by a period.

The 2nd worksheet shows a list of department number with department
names.

The 3rd worksheet shows a list of account numbers with account names.

Within the 1st worksheet, I need to take the account code and cross
reference it with the department number in the 2nd worksheet and the
account number in the 3rd worksheet so that a department name + account
name is inserted next to the account code.

For example:

Sheet 1:
Account code
81500.5820.23100

Sheet 2:
Dept code Name
5820 Shipping

Sheet 3:
Account Code Name
23100 Supplies

I need a formula that will construct a name next to the account code in
sheet 1 that would look like this:

Sheet 1:
Account code Name
81500.5820.23100 Shipping.Supplies

Any help on this will be greatly appreciated!

Thanks,

Gary

 

[Biff]

Are the Account codes *ALWAYS* 3 groups of numbers delimited by "dots" ?

Account code
81500.5820.23100

Are the Dept codes *ALWAYS* 4 digits?

Dept code
5820

Are the "other" Account codes *ALWAYS" 5 digits?

Account Code
23100

Biff

 

[NIMISH]

Gary,

Have a look of VLOOKUP function. This what you exactly need.

Nimish.

PS : I will try to construct spread sheet and send it to you.

 

[ash.gary]

"Yes" to all three questions.

Thanks,

Gary

Are the Account codes *ALWAYS* 3 groups of numbers delimited by "dots" ?

Are the Dept codes *ALWAYS* 4 digits?

Are the "other" Account codes *ALWAYS" 5 digits?

Biff

 

[Biff]

Ok, a couple of other points I should have brought up......

81500.5820.23100

Is the first group of numbers (81500) always 5 digits?

Do the second and third groups (5820.23100) ever have leading zeros?

81500.0020.03100

We can use this for starters and tweak as needed:

Sheet1 A2 = 81500.5820.23100
Sheet2 A2:B10 = Dept codes and names
Sheet3 A2:B10 = Account Codes and names

Formula in Sheet1 B2:

=VLOOKUP(MID(A2,7,4)+0,Sheet2!A2:B10,2,0)&"."&VLOOKUP(RIGHT(A2,5)+0,Sheet3!A2:B10,2,0)

This assumes the first group of numbers (81500) is always 5 digits and the
second and third groups (5820.23100) never have leading zeros.

Biff

 

[ash.gary]

Your assumption is correct: no leading zero's.

The formula looks good.

Thanks for your help!

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

Your assumption is correct: no leading zero's.

The formula looks good.

Thanks for your help!