Table of Contents
VLOOKUP is a commonly used function in Excel that allows users to search for a specific value in a table and return a corresponding value from another column. However, it is not typically used to calculate the average of multiple rows. To calculate the average of multiple rows in Excel, the AVERAGE function should be used instead. This function takes a range of cells as its argument and returns the average of all the values in that range. Therefore, while VLOOKUP can be helpful for retrieving a specific value, it is not suitable for calculating averages.
Excel: Use VLOOKUP to Average Multiple Rows
You can use the following formulas to perform a VLOOKUP and average multiple rows in Excel:
Method 1: VLOOKUP and AVERAGE Values in First Matched Row
=AVERAGE(VLOOKUP(A14, $A$2:$D$11, {2,3,4}, FALSE))
This particular formula calculates the average of the values in columns 2, 3, and 4 in the first row of the range A2:D11 where the value in column A is equal to the value in cell A14.
Method 2: VLOOKUP and AVERAGE Values in All Matched Rows
=AVERAGE(IF(A2:A11=$A$14,B2:D11))
This particular formula calculates the average of the values in columns B, C, and D for each row where the value in column A is equal to the value in cell A14.
The following examples show how to use each method in practice with the following dataset in Excel that shows the points scored by various basketball players in three different games:
Example 1: VLOOKUP and Average Values in First Matched Row
We can type the following formula into cell B14 to average the points values scored by Chad in all three games:
=AVERAGE(VLOOKUP(A14, $A$2:$D$11, {2,3,4}, FALSE))
Once we press Enter, the results will be shown:
This formula uses a VLOOKUP to find “Chad” in the Player column and then returns the average of the points values for each game in the first row that matches Chad.
We can see that the formula returns a value of 13.33, which is the average of the points scored by Chad in the first row where “Chad” appears.
Example 2: VLOOKUP and Average Values in All Matched Rows
=AVERAGE(IF(A2:A11=$A$14,B2:D11))
Once we press Enter, the results will be shown:
This formula looks up “Chad” in the Player column and then returns the average of the points values for each game in each row that matches Chad.
We can see that Chad scored an average of 17 points per game across the two rows he appeared in.
Additional Resources
The following tutorials explain how to perform other common operations in Excel: