Excel assign text a numerical value

Question

Everyone is saying =Lookup is the formula i need but every time i try it its not functioning as I want it to. I believe I really need to define my text a numerical value instead of just looking up and displaying a cell from a chart. 

I am trying to apply to school of nursing which uses a point system to evaluate its applicants. I am wanting to send a formal spreadsheet to the pre-nursing advisor to help me with what course I should be taking to achieve the most points possible. 

This is what I want the final chart to look like:

Course	Grade	Points
Human Anatomy	B	15
Human Physiology	A-	18.5

The grades values are:

A   = 4.0

A-  = 3.7

B+ = 3.3

B    = 3.0

B-  = 2.7

C+ = 2.3

C   = 2.0

I want to the points Column(C) to have the Function:

=B2*5

=B3*5

So how do I make text A- have a value so I can type A- in a cell and reference that cell within a formula to provide a total numerical value when formula is executed in that cell.

Answer 1

Just change the formula in E to include *5 at the end:

=VLOOKUP(D2,$A$2:$B$8,2,FALSE)*5

And delete column F.

Answer 2

Create a table of letter grades versus points in A2:B8, like in this picture:

And then for a letter grade entered in D2, use this formula in E2:

=VLOOKUP(D2,$A$2:$B$8,2,FALSE)

And to get the points, use =E2*5 in cell F2

Answer 3

He Garret

Please, try the following steps as shown in the picture below

1- Create anywhere on your workbook (can be in another sheet) a table with Grade list and it respective Points (values)

2- Give to the table a Named Range  (ex "GradeTable")

3- Use the formula below to get the results you expect

=IFERROR(VLOOKUP(B2,GradeTable,2,FALSE)*5,"")

and copy/drag it down

Do let me know if you need more help

On the other hand,

If the answer helped you.

Please, consider marking this thread as answered.

It would help others in the community with similar questions or problems.

Thank you in advance

Regards

Jeovany

Answer 4

Hi Garrett,

 May I know if you still need help? If so, you can post back and we will try our best to help you.

Best Regards,

Sukie

Answer 5

Is there a way to get this to work without having Column E? displaying the Value is redundant on the same page when you can already reference the table to see the Value. It just clutters the Table I am making.