Select the name or the data that you want to find the result from the dataset and put the name or the data in another cell.
HOW TO USE VLOOKUP IN EXCEL TO COMPUTE TOTAL HOW TO
Let’s figure out how to look in different columns and get the sum result of matching values in those columns using VLOOKUP SUM functions in Excel. What if you want to find out just a particular student’s total marks? To get that, you have to calculate numbers based on different columns. VLOOKUP and SUM to Calculate the Matching Values in ColumnsĬonsider the following dataset which consists of students’ names and their obtained marks on each course stored in different columns. In this section, we will learn how to utilize VLOOKUP and SUM functions in Excel together to generate certain results. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.The data range that you want to search your valueįALSE (or 0) means exact match and TRUE (or 1) means approximate match.Ħ Useful Methods to Utilize VLOOKUP with SUM Function in Excel If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. Ĭopying our formula to cells F4 to F6 returns the grades for the scores 86, 75 and 58.įigure 4. In our example, since 91 cannot be found in column B, our formula returns the grade for the value less than 91, which is 90.
If it doesn’t find an exact match, it will return the value closest to and less than the lookup value. Our formula searches for the value 91 in the first column of our lookup table B3:C11, then returns the corresponding grade in column C. Range_lookup is TRUE because we want to find either an exact or approximate match. “Grade” is in the second column of the table array so col_index_num is 2. Our lookup_value is cell E3, which contains the score we want to search for: “ 91 ”. Lookup grades using VLOOKUP and approximate match The dollar signs “$” in the formula fix the cells so that we can easily copy and paste the formula to other cells.įigure 3. Step 4 : Copy the formula in cell F3 to cells F4:F6 by clicking the “+” icon at the bottom-right corner of cell F3 and dragging it down Enter the formula: = VLOOKUP ( E3, $B$3:$C$11, 2, TRUE ) In order to look up the scores and obtain the grade, we will be using VLOOKUP with an approximate match. We want to determine the corresponding grade for the given scores in column E. It is important to remember that the table must be sorted in ascending order for the VLOOKUP formula to function properly with an approximate match. Sample data to calculate grades with VLOOKUP In cells F3 to F6, we want to determine the corresponding grade for each given score by using VLOOKUP with an approximate match.įigure 2. In cells E3 to E6, we enter the scores we want to look up from our data in B3:C11. Our data consists of two columns: Score (column B) and Grade (column C).
table_array – the range of cells in the source table containing the data we want to retrieve.lookup_value – the value that we want to search and find in the table_array.The parameters of the VLOOKUP function are: = VLOOKUP ( lookup_value, table_array, col_index_num, ) Final result: Calculate grades with VLOOKUPįinal formula: =VLOOKUP(E3,$B$3:$C$11,2,TRUE) Syntax of the VLOOKUP function This step by step tutorial will assist all levels of Excel users to calculate grades with VLOOKUP.įigure 1. VLOOKUP provides a quick way of looking up a value from any list or range. While working with Excel, we are able to lookup a value and retrieve data from a data set using the VLOOKUP function.
How to Calculate Grades with VLOOKUP in Excel