help with formulas???

N

Natasha

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
 
J

Jacob Skaria

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
 
R

Ross

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top