Fill a seperate cell with info depending on the input in another c


G

Guest

I hope I can put this understandably.

I have 2 columns (A and B) on one worksheet and 2 columns (a2 and b2) and a
second worksheet.

In Column a2 is a range of values (alpha,beta,charlie...zulu) named "list"
In Column b2 is a range of associated costs (1,2,3,4....26)

Column A has a data valadation field using a drop-down list that allows the
user to choose a name (alpha,beta...)

What I need is for Column B to automaticly fill with the associated cost
depending on whichever value is chosen in Coumn A (so if alpha is chose the
adjacent cell is filled with 1, beta..2, charlie..3)

I can not figure out how to do this. The closest I can come is a very ugly
nested IF statement but even that doesn't work as I can't nest 26 IF
statments. Any help would be wonderfull.
 
Ad

Advertisements

Ad

Advertisements

G

Guest

Use the VLOOKUP function like this...

=VLOOKUP(A1,{"alpha",1;"beta",2;"charlie",3},2)

or you can insert a small table like this...

C D
1 alpha 1
2 beta 2
3 charlie 3
4 ...

and use a formula like this...

=VLOOKUP(A1,C1:D3,2)

you should be able to expand both formulas to include the entire selection.
 

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