I am creating a spreadsheet that uses our 7 digit client number to track who
the client actually belongs to depending on the first 2 digits of the number.
For example, the number could be 10-50000 where the 10 designates that it is
Jane's client or 12-50000 in which the 12 designates that it is Mary's
client. I'd like to be able to only enter the client # in one cell, then
have the name auto fill in the next cell. Any tips on a formula that will
get that done for me? Thanks!
Mellissa

You can achieve this by using VLOOKUP

Create the list of names. Assume that ColA contains the number and B the name
ColA ColB
10 Jane
12 Mary

Now try
D1 = 12
E1 Enter formula
=VLOOKUP(D1,A:B,2,FALSE)

If this post helps click Yes
---------------
Jacob Skaria


"MellissaD" wrote:

I am creating a spreadsheet that uses our 7 digit client number to track who
the client actually belongs to depending on the first 2 digits of the number.
For example, the number could be 10-50000 where the 10 designates that it is
Jane's client or 12-50000 in which the 12 designates that it is Mary's
client. I'd like to be able to only enter the client # in one cell, then
have the name auto fill in the next cell. Any tips on a formula that will
get that done for me? Thanks!
Mellissa

MellissaD wrote:
I am creating a spreadsheet that uses our 7 digit client number to track who
the client actually belongs to depending on the first 2 digits of the number.
For example, the number could be 10-50000 where the 10 designates that it is
Jane's client or 12-50000 in which the 12 designates that it is Mary's
client. I'd like to be able to only enter the client # in one cell, then
have the name auto fill in the next cell. Any tips on a formula that will
get that done for me? Thanks!
Mellissa


You could use INDEX MATCH

In this example the Client Owner is entered in E2 and Client Number in F2

12-1000 AB 13 1000 CD
13-1000 CD
12-1001 EF
13-1001 GH




=INDEX($D$2:$D$5,MATCH(E2&"-"&F2,$C$2:$C$5,FALSE))

<
edvwvw

--
Message posted via http://www.officekb.com

Thank you. It works if I enter the client number in two seperate rows. One
cell for the "10" or incharge and one cell for the remaining 5 digits rather
than entering the whole number like this 10-50000. This is definitely
useable but is it possible to do it all with one?

"Jacob Skaria" wrote:

You can achieve this by using VLOOKUP

Create the list of names. Assume that ColA contains the number and B the name
ColA ColB
10 Jane
12 Mary

Now try
D1 = 12
E1 Enter formula
=VLOOKUP(D1,A:B,2,FALSE)

If this post helps click Yes
---------------
Jacob Skaria


"MellissaD" wrote:

I am creating a spreadsheet that uses our 7 digit client number to track who
the client actually belongs to depending on the first 2 digits of the number.
For example, the number could be 10-50000 where the 10 designates that it is
Jane's client or 12-50000 in which the 12 designates that it is Mary's
client. I'd like to be able to only enter the client # in one cell, then
have the name auto fill in the next cell. Any tips on a formula that will
get that done for me? Thanks!
Mellissa