excel formula

hi experts,

I am looking at making a spread sheet that will allow me to put into a cell a letter between 'a' and 'f' and it will return a value of my choice.

what formula is used for this, and can anyone tell me step by step how to do this. I would google it but I cant seem to get my terminology correct.
LVL 2
peggiegregAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>I am looking at making a spread sheet that will allow me to put into a cell a letter between 'a' and 'f' and it will return a value of my choice.

what kind of value to be returned from your choice? can you be more specific in this?

like, if "a" was selected, what value to be returned? into another cell, etc?
peggiegregAuthor Commented:
so for example, if a is selected I want it to say £1,500 and if you select b I want it to say £1700 etc... the values I can change once I have the formula in place.

then after that I wanted to be able to put in a postcode and for it to provide you with a letter for example if I entered SG2 9XR then it would work out what the first 2 letters were 'SG' combined with whatever number is entered in this example '2' and it will give you a letter.
Rob HensonFinance AnalystCommented:
Set up a small table with required values:

      A      B
2      a      1500
3      b      1700
4      c      1900
5      d      2100
6      e      2300
7      f      2500

The uppercase letters and single digit numbers represent column and row headers, ie a range A2 to B7

Then use a lookup formula:

=VLOOKUP(C3,$A$2:$B$7,2,false)

That will find the value in C3 from list starting at A2 and return value from list starting in B2.
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

[ fanpages ]IT Services ConsultantCommented:
How does "a is selected" relate to £1,500?
Similarly, what is the connection between "select b" and £1700?

Do you mean possible values in a lookup table could be, for example...

A 1500
B 1700
C 1900
D 2150
E 2795

?

Also, once the first two characters of "SG2 9XR" have been extracted, & combined with, I presume, a user-specified value of "2", what do you mean by "it will give you a letter"?

Is this also a lookup table, matching "SG" + "2" with a distinct value?

SG1 North Stevenage including Old Town & Town Centre
SG2 South Stevenage, Walkern, Ardeley
SG3 Knebworth, Datchworth, Woolmer Green
(& so on)

Also see: [ https://en.wikipedia.org/wiki/SG_postcode_area#coverage ]
peggiegregAuthor Commented:
thanks I will try what has been suggested so far and the reason for the request is I work for an insurance broker, one scheme we offer to customers has to be manually worked out and I wanted to try and automate it slightly.

so the first thing we do is use there postcode to determine a letter so for example the guide says SG 1-3 is cat 7 and SG 4-6 is cat 4.

the Cat changes the base rate we work from so if the postcode is cat 4 then it will start with a base rate off £XXXX.XX

what I am trying to achieve is someone can pop this postcode in and it will tell you the base rate.

hope this makes sense
[ fanpages ]IT Services ConsultantCommented:
Yes, that is a little clearer.  Thanks :)

OK, so it sounds like you could implement a "lookup table" for the base rate associated with a set of categories.

You also need some way of determining the category associated with a postcode.  That can also be a "lookup table".

Please see a very simple example within the attached workbook.
Q-28705234.xls

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.