# Solved: need MS Excel=IF conditional formula help pls

Discussion in 'Business Applications' started by alexhhx, Jun 30, 2008.

Not open for further replies.

Joined:
Jun 30, 2008
Messages:
2
i had read some other posts which are related to this formula, but i still have no idea at all.

Lets say i have 2 column now, O7 represent the Result and as for P7 represent Grade.
Now what i want to do is when i enter number into O7 column, i want it to show the grade that i want at P7 column.

> 80 A+
75-79 A
70-74 B+
65-69 B
60-64 C+
55-59 C
50-54 D+
40-49 D
< 40 E

Sorry for my lousy english, hope you guys can understand what i'm trying to say

2. ### The Villan

Joined:
Feb 20, 2006
Messages:
2,255
Open up the attached spreadsheet. I have used A1 and B1 instead of O7 and P7

A1 is the input cell and B1 has a function in it =VLOOKUP

Read the instructions in column G to explain how it works.

You can put whatever number you like in A1 and the Vlookup function, will return the correct grade based on a lookup table in Columns D & E

Hope that helps.

3. ### The Villan

Joined:
Feb 20, 2006
Messages:
2,255
The file is not uploading although I know it is below the max limit. Can a techie look into thsi error please.

In the meantime, what yuou need is a VLOOKUP function that goes into P7

=VLOOKUP(O7,\$AA\$1:\$AB\$10,2)

You create the Lookup table in AA1 to AB10 exactly as below.
Col AA Col AB

0 E
40 D
50 D+
55 C
60 C+
65 B
70 B+
75 A
80 A+

The Vlookup function in P7 looks at the value in cell O7 and then finds the nearest number in column AA
If as an example 39 is entered into O7, the formula will look down column AA
It will look and when it finds a number higher than it, it goes to the lower number in column AA
So it will find 0 and then it will return the grade in E2 (column 2 of the lookup table)

Hope that helps a bit. Its a ****** that I cannot upload an example spreadsheet.

Joined:
Jun 30, 2008
Messages:
2
The vlookup function works great!
Thanks a lot, The Villan!!

5. ### The Villan

Joined:
Feb 20, 2006
Messages:
2,255
You are very welcome

As Seen On