Avatar of PeggieGreg
PeggieGreg
 asked on

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.
Microsoft Excel

Avatar of undefined
Last Comment
[ fanpages ]

8/22/2022 - Mon
Ryan Chong

>>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?
PeggieGreg

ASKER
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 Henson

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
[ fanpages ]

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 ]
PeggieGreg

ASKER
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
ASKER CERTIFIED SOLUTION
[ fanpages ]

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.