help with formulas???
 

Hi,

I am pretty new to Excel. I want to be able to import data from a number of
client trading reports and the client's account number needs to be converted
into an advisor's name. Say I have 20 advisors, and each advisor has 50
clients, I need to get excel to put the appropriate adviser's name next their
client's account number if it appears in column A.

For example, if I import the below data, I would like to create a formula
which says "if A1 = U11111 then enter the word "Megan" appears in B2", "if A1
= u11112 then enter the word "Jane" in B2" and so on. I will need to create
about 1000 if statements for each row in the column A, which will result in a
different name in column B.


A1 U11111 B1 *advisor's name*
A2 U11112 B2 *advisor's name*
A3 U39393 B3 *advisor's name*
A4 U23893 B4 *advisor's name*
A5 U23930 B5 *advisor's name*
A6 U39290 B6 *advisor's name*
and so on...
Your help is greatly appreciated.

Natasha

help with formulas???
 

1. Create the unique list in a separate sheet say Sheet2 with the valid codes
in ColA and corresponding names in ColB
2. In your current sheet say Sheet1 in B1 use this formula
=VLOOKUP(A1,Sheet2!A:B,2,FALSE)

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


"Natasha" wrote:

Hi,

I am pretty new to Excel. I want to be able to import data from a number of
client trading reports and the client's account number needs to be converted
into an advisor's name. Say I have 20 advisors, and each advisor has 50
clients, I need to get excel to put the appropriate adviser's name next their
client's account number if it appears in column A.

For example, if I import the below data, I would like to create a formula
which says "if A1 = U11111 then enter the word "Megan" appears in B2", "if A1
= u11112 then enter the word "Jane" in B2" and so on. I will need to create
about 1000 if statements for each row in the column A, which will result in a
different name in column B.


A1 U11111 B1 *advisor's name*
A2 U11112 B2 *advisor's name*
A3 U39393 B3 *advisor's name*
A4 U23893 B4 *advisor's name*
A5 U23930 B5 *advisor's name*
A6 U39290 B6 *advisor's name*
and so on...
Your help is greatly appreciated.

Natasha

help with formulas???
 

Previous response from Jacob is spot on.
Only I'd suggest using absolute values for your lookup.
So for example if your table on Sheet2 goes from row 2 to row 150 use
=VLOOKUP(A1,Sheet2!$A$2:$B$150,2,FALSE)
Jacob's way will work absolutely fine though but my version will allow you
to use other cells in columns A and B of Sheet2 without getting false data
reporting back.

Kiss, kiss.

"Jacob Skaria" wrote:

1. Create the unique list in a separate sheet say Sheet2 with the valid codes
in ColA and corresponding names in ColB
2. In your current sheet say Sheet1 in B1 use this formula
=VLOOKUP(A1,Sheet2!A:B,2,FALSE)

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


"Natasha" wrote:

Hi,

I am pretty new to Excel. I want to be able to import data from a number of
client trading reports and the client's account number needs to be converted
into an advisor's name. Say I have 20 advisors, and each advisor has 50
clients, I need to get excel to put the appropriate adviser's name next their
client's account number if it appears in column A.

For example, if I import the below data, I would like to create a formula
which says "if A1 = U11111 then enter the word "Megan" appears in B2", "if A1
= u11112 then enter the word "Jane" in B2" and so on. I will need to create
about 1000 if statements for each row in the column A, which will result in a
different name in column B.


A1 U11111 B1 *advisor's name*
A2 U11112 B2 *advisor's name*
A3 U39393 B3 *advisor's name*
A4 U23893 B4 *advisor's name*
A5 U23930 B5 *advisor's name*
A6 U39290 B6 *advisor's name*
and so on...
Your help is greatly appreciated.

Natasha