How to VLOOKUP and AVERAGE Specific Values in Excel

Whenever someone needs to calculate the average of VLOOKUP values from a worksheet then you can use Excel functions. In this article, I’m going to explain how to calculate VLOOKUP AVERAGE in Excel. To make it more visible I’m going to use a dataset of sales information from different regions. There are 4 columns in the dataset which are Sales Rep, Region, Product, and Sales. Here these columns represent the total sales information for a particular product by a sales representative. Look at the overview before jumping into the methods.

How to Use VLOOKUP to Calculate AVERAGE of Specific Values in Excel: 6 Quick Ways

1. Merge VLOOKUP & AVERAGE Functions in Excel

To calculate the average you can use the AVERAGE function within the VLOOKUP function. VLOOKUP will search the value and the AVERAGE function will calculate the average of lookup values.

First, select the cell to place your resultant value.
➤ Here, I selected cell G4.
Then, type the following formula in the selected cell or into the Formula Bar.

Here, from the Sales and Sales Person column, this function will look up the average sales value and will return the Sales Person name.

Finally, press the ENTER key.
Now, from the selected cells of the column, it will show the name of the salesperson whose sales amount matched the average sales.

Read More: How to Find Average with OFFSET Function in Excel

2. Use of AVERAGEIF Function to VLOOKUP the AVERAGE of Specific Criteria

You can use the AVERAGEIF function to calculate the average lookup value. From the selected column, it will search the lookup value to calculate the average of these values.

Firstly, select the cell where you want to place your result.
➤ Here, I selected cell H4.
Then, type the following formula in the selected cell or into the Formula Bar.

Here, I selected the range C4:C15 of the Location column next as criteria used G4 cell then selected the cell range E4: E15 of the Sales column as an average_array. Finally, it will return the average value of the lookup value.

Finally, press the ENTER key.
Eventually, it will show the average value of the given lookup value.

Later, you can use the Fill Handle to AutoFill the formula for the rest of the cells of the LOOKUP column.

3. Combine AVERAGE & IF Functions in Excel

You can use the IF function within the AVERAGE function to calculate the average lookup value.

Firstly, select the cell where you want to place your result.
➤ Here, I selected cell H4.
Then, type the following formula in the selected cell or into the Formula Bar.

Here, the IF function will fetch the values for the selected G4 cell using the logical_test. Then the AVERAGE function will calculate the average values of the USA.

After that, press the ENTER key.
Now, it will show the average value of the given lookup value.

Finally, you can use the Fill Handle to AutoFill formula for the rest of the cells of the LOOKUP column.

4. Join AVERAGE and MATCH Functions to Lookup Values and Calculate Average

By using the AVERAGE function along with the IF, ISNUMBER, and MATCH functions you can calculate the average of lookup values.

Firstly, select the cell to place your result.
➤ Here, I selected cell H4.
Then, type the following formula in the selected cell or into the Formula Bar.

Here, the MATCH function will match the values for the selected G4 cell from the Location column and then pass the value to ISNUMBER. Then the IF function will apply the logical_test in the range E4: E15. Finally, it will calculate the average values of the USA.

At last, press the ENTER key.
Now, it will show the average value of the given lookup value of the USA.

Finally, you can use the Fill Handle to AutoFill formula for the rest of the cells of the LOOKUP column.

5. Apply AVERAGE & VLOOKUP in Excel

You can use the VLOOKUP function within the AVERAGE function to calculate the average for the lookup value.

Firstly, select the cell where you want to place your result.
➤ Here, I selected cell H4.
Then, type the following formula in the selected cell or into the Formula Bar.

Here, the VLOOKUP function will fetch the values for the selected G4 cell from the Location column for the selected range B4:E8. Then the AVERAGE function will calculate the average values of the USA.

Finally, press the ENTER key.
By now, it will show the average value of the given lookup value of the USA.

Currently, you can use the Fill Handle to AutoFill the formula for the rest of the cells of the LOOKUP column.

6. Merge SUMIF & COUNTIF Functions in Excel

You can use SUMIF and COUNTIF functions to calculate the average for the lookup value.

To begin with, select the cell where you want to place your result.
➤ Here, I selected cell H4.
Then, type the following formula in the selected cell or into the Formula Bar.

Here, the SUMIF function will fetch the values for the selected G4 cell and will calculate the sum of those values. Then the COUNTIF function will count how many times the selected G4 cell occurred. Finally, the sum of the values will be divided by the count.

Finally, press the ENTER key.
Here, it will show the average value of the given lookup value of the USA in the selected cell.

Finally, you can use the Fill Handle to AutoFill formula for the rest of the cells of the LOOKUP column.

Practice Section of VLOOKUP AVERAGE in Excel

I’ve given a practice sheet in the workbook to practice these explained ways of how to calculate VLOOKUP AVERAGE. You can download it from the above.

Also, practice this.

Download Workbook

Conclusion

In this article, I tried to explain 6 easy and quick ways to calculate VLOOKUP AVERAGE in Excel. These different ways will help you to perform VLOOKUP AVERAGE. Last but not least, if you have any kind of suggestions, ideas, or feedback please feel free to comment below.

Related Articles

• How to Calculate Average in Excel Excluding 0
• How to Average Values Greater Than Zero in Excel
• How to Use VBA Average Function in Excel
• Calculate the Average of an Array with VBA
• How to Add Average Line to Excel Chart

<< Go Back to Calculate Average in Excel | How to Calculate in Excel | Learn Excel