For us, this is the score in column A, starting with cell A2. So for our first entry, the specific formula would be: Before we start applying the formula to our example, lets quickly recall the syntax for VLOOKUP: VLOOKUP (lookupvalue, tablearray, colindexnum, rangelookup) In that formula, the variables work like this: lookupvalue - This is the value you are looking for. For our example, that is on the 2nd column, so the value is 2. For our example above, that is E2:F8.Ĭolumn_containing_the_equivalent - the column number within the range that contains the conversion table.
Range_of_the_conversion_table - the range of the conversion table. For our example above, it’s from B2 to B13. Where the following are defined as follows:Ĭell_containing_the_score - points to the cell containing the score. =VLOOKUP(cell_containing_the_score, range_of_the_conversion_table, column_containing_the_equivalent) To assign a grade based on a score, you can use the VLOOKUP function. For this trick, we will use the following syntax of VLOOKUP: Calculate grades with VLOOKUP Generic formula. Also, you should sort out the table in an ascending order for acquiring optimal. It's the perfect function to use when your data includes, e.g., company names and locations, different types of products with descriptions, or full-time employment ranges and average salaries per employee. However, a user should simply configure a table, which serves as a ‘key’ (refer to the formula), scores in the left column and the corresponding grades in the right column. VLOOKUP is a function in Microsoft Excel that lets you search for information in one table and find related information in another table.
For this trick to work, you need to list down the conversion from the lowest value to the highest value.Īs mentioned in the title of this short tutorial, we will use VLOOKUP. Using VLOOKUP function allows a user to calculate grades on an Excel Spreadsheet easily.