# Set a letter to represent a value

#### Mandie09

##### New Member
I need to be able to type in 0, S, or D into a cell and it stand for a number value such as, 0=0, S=1.5, D=3. I need to be able to change this cell to any of those 3 and it automatically change the number value. In the cells I already have =SUM(G5*H2+G2) all the way through H17. I need to be able to change G2 to either 0, S, or D and it change the value accordingly.

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

#### pgc01

##### MrExcel MVP
Hi

Use:

=SUM(G5*H2+LOOKUP(2,1/(G2={0,"D","S"}),{0,3,1.5}))

#### Rick Rothstein

##### MrExcel MVP
Another way...

=G5*H2+1.5*(SEARCH(G2,"0SD")-1)

You did not really need the SUM function call as you are already adding the numbers directly using the + sign.

Last edited:

#### pgc01

##### MrExcel MVP
Thank you Rick, I missed it.

=G5*H2+LOOKUP(2,1/(G2={0,"D","S"}),{0,3,1.5})

#### Mandie09

##### New Member

This is perfect! You are a life saver! Thank you!

#### Mandie09

##### New Member
What does the 2,1 represent?

#### Rick Rothstein

##### MrExcel MVP
What does the 2,1 represent?
The 2 is the first argument to the LOOKUP function, the comma delimits the first argument from the second argument, the 1 is the numerator for the fraction that makes up the second argument to the LOOKUP function, namely this...

1/(G2={0,"D","S"})

Just out of curiosity, did you see the formula I posted in Message #3 (you may find it easier to figure out how it works)?

Replies
8
Views
220
Replies
5
Views
144
Replies
3
Views
130
Replies
26
Views
1K
Replies
5
Views
79

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,151,979
Messages
5,767,432
Members
425,412
Latest member
andrealp4444

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back