Function that will look at letter in a column and return a number
 

I am making a spreadsheet of what's withheld from employee's paychecks (every
2 weeks) (e.g., health insurance premiums, Flex Spending, etc).


A B C
1 John (F)
2 Mary (S)


Column B lists what kind of coverage the employee has--"F" is Family, "S" is
Single, "C" is Couple--each coverage has a different premium (price).

I need a function for column C that will look at cell B1 and see that "F"
means John subscribes to Family Coverage, and return (to column C) a
particular value; look at cell B2 and see that "S" means Mary subscribes to
Single coverage, and return a different value.

Is there a function/formula that I can utilize to reach this goal? Please,
if you're answering, explain in Lehman's terms. Thanks in advance!

Function that will look at letter in a column and return a number
 

Try the below formula in B1..Hope you will have only these 3 entries in B1.
Lookup will look at the value in the 1st array which is f,s,c and return the
corresponding value in the next array which is 30,10,20 respectively...

=IF(B1="","",LOOKUP(B1,{"F","S","C"},{30,10,20}))

If this post helps click Yes
---------------
Jacob Skaria


"locasciok1" wrote:

I am making a spreadsheet of what's withheld from employee's paychecks (every
2 weeks) (e.g., health insurance premiums, Flex Spending, etc).


A B C
1 John (F)
2 Mary (S)


Column B lists what kind of coverage the employee has--"F" is Family, "S" is
Single, "C" is Couple--each coverage has a different premium (price).

I need a function for column C that will look at cell B1 and see that "F"
means John subscribes to Family Coverage, and return (to column C) a
particular value; look at cell B2 and see that "S" means Mary subscribes to
Single coverage, and return a different value.

Is there a function/formula that I can utilize to reach this goal? Please,
if you're answering, explain in Lehman's terms. Thanks in advance!

Function that will look at letter in a column and return a number
 

=IF(B1="","",LOOKUP(B1,{"F","S","C"},{30,10,20} ))

The lookup_vector *must* be sorted in ascending order.

=IF(B1="","",LOOKUP(B1,{"C","F","S"},{20,30,10}))

--
Biff
Microsoft Excel MVP


"Jacob Skaria" wrote in message
...
Try the below formula in B1..Hope you will have only these 3 entries in
B1.
Lookup will look at the value in the 1st array which is f,s,c and return
the
corresponding value in the next array which is 30,10,20 respectively...

=IF(B1="","",LOOKUP(B1,{"F","S","C"},{30,10,20}))

If this post helps click Yes
---------------
Jacob Skaria


"locasciok1" wrote:

I am making a spreadsheet of what's withheld from employee's paychecks
(every
2 weeks) (e.g., health insurance premiums, Flex Spending, etc).


A B C
1 John (F)
2 Mary (S)


Column B lists what kind of coverage the employee has--"F" is Family, "S"
is
Single, "C" is Couple--each coverage has a different premium (price).

I need a function for column C that will look at cell B1 and see that "F"
means John subscribes to Family Coverage, and return (to column C) a
particular value; look at cell B2 and see that "S" means Mary subscribes
to
Single coverage, and return a different value.

Is there a function/formula that I can utilize to reach this goal?
Please,
if you're answering, explain in Lehman's terms. Thanks in advance!

Function that will look at letter in a column and return a number
 

Another way:

Using your chart,
If you assign costs to each letter such as F= $1000 and so on, place F in
cell f1, S in cell f2, and C in cell f3, etc. with the corresponding dollars
in the g column, in cell c1 put:

=IF(B1="","",VLOOKUP(B1,($F$1:$G$20),2,FALSE))

and then drag/copy it down. (I used $G$20 to give you up to 20 entries.)

You can then adjust the dollar amounts in g if you need to. If you need more
entries place the letters in column f, with the dollar amounts in column g.
Again, the $g$20 will cover 20 entries. If you need more change the 20 to
however many you need, and drag/copy the formula down again.

Squeaky


"locasciok1" wrote:

I am making a spreadsheet of what's withheld from employee's paychecks (every
2 weeks) (e.g., health insurance premiums, Flex Spending, etc).


A B C
1 John (F)
2 Mary (S)


Column B lists what kind of coverage the employee has--"F" is Family, "S" is
Single, "C" is Couple--each coverage has a different premium (price).

I need a function for column C that will look at cell B1 and see that "F"
means John subscribes to Family Coverage, and return (to column C) a
particular value; look at cell B2 and see that "S" means Mary subscribes to
Single coverage, and return a different value.

Is there a function/formula that I can utilize to reach this goal? Please,
if you're answering, explain in Lehman's terms. Thanks in advance!

Function that will look at letter in a column and return a num
 

Oops. my mistake. Thanks Biff.

If this post helps click Yes
---------------
Jacob Skaria


"T. Valko" wrote:

=IF(B1="","",LOOKUP(B1,{"F","S","C"},{30,10,20} ))

The lookup_vector *must* be sorted in ascending order.

=IF(B1="","",LOOKUP(B1,{"C","F","S"},{20,30,10}))

--
Biff
Microsoft Excel MVP


"Jacob Skaria" wrote in message
...
Try the below formula in B1..Hope you will have only these 3 entries in
B1.
Lookup will look at the value in the 1st array which is f,s,c and return
the
corresponding value in the next array which is 30,10,20 respectively...

=IF(B1="","",LOOKUP(B1,{"F","S","C"},{30,10,20}))

If this post helps click Yes
---------------
Jacob Skaria


"locasciok1" wrote:

I am making a spreadsheet of what's withheld from employee's paychecks
(every
2 weeks) (e.g., health insurance premiums, Flex Spending, etc).


A B C
1 John (F)
2 Mary (S)


Column B lists what kind of coverage the employee has--"F" is Family, "S"
is
Single, "C" is Couple--each coverage has a different premium (price).

I need a function for column C that will look at cell B1 and see that "F"
means John subscribes to Family Coverage, and return (to column C) a
particular value; look at cell B2 and see that "S" means Mary subscribes
to
Single coverage, and return a different value.

Is there a function/formula that I can utilize to reach this goal?
Please,
if you're answering, explain in Lehman's terms. Thanks in advance!


.