Link to home
Start Free TrialLog in
Avatar of 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.
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

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


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.
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:


That will find the value in C3 from list starting at A2 and return value from list starting in B2.
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: [ ]
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
Avatar of [ fanpages ]
[ fanpages ]

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial