Let’s say you have the following grades:

Score | Grade | Grade |

[0,6] | 1 | E |

(6,8] | 2 | D |

(8,10] | 3 | C |

(10,12] | 4 | B |

(12,infinity) | 5 | A |

You have the final score for a student, and you want to **automate the grade calculation in Excel**.

You have to use a function. The function you need is **VLOOKUP**.

You can use the

VLOOKUPfunction to search the first column of a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) of cells, and then return a value from any cell on the same row of the range.The V in

VLOOKUPstands for vertical. UseVLOOKUPinstead ofHLOOKUPwhen your comparison values are located in a column to the left of the data that you want to find.VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_valueRequired. The value to search in the first column of the table or range. Theargument can be a value or a reference. If the value you supply for the llookup_valueargument is smaller than the smallest value in the first column of theookup_valueargument,table_arrayVLOOKUPreturns the #N/A error value.

table_arrayRequired. The range of cells that contains the data. You can use a reference to a range (for example,A2:D8), or a range name. The values in the first column ofare the values searched bytable_array. These values can be text, numbers, or logical values. Uppercase and lowercase text are equivalent.lookup_valuecol_index_numRequired. The column number in theargument from which the matching value must be returned. Atable_arrayargument of 1 returns the value in the first column incol_index_num; atable_arrayof 2 returns the value in the second column incol_index_num, and so on.table_arrayIf the

argument is:col_index_num

- Less than 1,
VLOOKUPreturns the #VALUE! error value.- Greater than the number of columns in
,table_arrayVLOOKUPreturns the #REF! error value.range_lookupOptional. A logical value that specifies whether you wantVLOOKUPto find an exact match or an approximate match:

- If
is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less thanrange_lookupis returned.lookup_value

ImportantIfis either TRUE or is omitted, the values in the first column ofrange_lookupmust be placed in ascending sort order; otherwise,table_arrayVLOOKUPmight not return the correct value.For more information, see Sort data.

If

is FALSE, the values in the first column ofrange_lookupdo not need to be sorted.table_array- If the
argument is FALSE,range_lookupVLOOKUPwill find only an exact match. If there are two or more values in the first column ofthat match thetable_array, the first value found is used. If an exact match is not found, the error value #N/A is returned.lookup_value

So in my case the function is

=VLOOKUP(T3;$X$3:$Y$7;2) |

It means I want to lookup the value of cell *T3* in the range, and pick the result from the second column. The range contains the score and grade cells without the column headers.

The lookup table should be

Score |
Grade |
Grade |

0 | 1 | E |

6,1 | 2 | D |

8,1 | 3 | C |

10,1 | 4 | B |

12,1 | 5 | A |

*score*column values should be in increasing order. The

*score*value column should contain the smallest numbers in the range.