How to Calculate VLOOKUP AVERAGE in Excel (6 Quick Ways)

Using AVERAGE & VLOOKUP

Whenever someone needs to calculate the average of VLOOKUP values from a worksheet then you can use some 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 of the 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.

sample dataset

Download to Practice

6 Ways to VLOOKUP AVERAGE

1. Using VLOOKUP & AVERAGE Function

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 the cell G4
Then, type the following formula in the selected cell or into the Formula Bar.

=VLOOKUP(AVERAGE(B4:B15),B4:C15,2,TRUE)

Using Average & VLOOKUP

Here, from the Sales and Sales Person column, this function will lookup 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 Sales Person whose sales amount matched the average sales.

Using VLOOKUP & AVERAGE Function

2. Using AVERAGEIF Function

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 the cell H4
Then, type the following formula in the selected cell or into the Formula Bar.

=AVERAGEIF($C$4:$C$15,G4,$E$4:$E$15)

Using AVERAGEIF

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

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

Using AVERAGEIF

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

Using AVERAGEIF

3. Using AVERAGE & IF 

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 the cell H4
Then, type the following formula in the selected cell or into the Formula Bar.

=AVERAGE(IF($C$4:$C$15=G4,$E$4:$E$15))

Using AVERAGE & IF

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.

Using AVERAGE & IF

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

Using AVERAGE & IF

Read more: Running Average: How to Calculate Using Excel’s Average(…) Function

4. Using AVERAGE and MATCH

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

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

=AVERAGE(IF(ISNUMBER(MATCH($C$4:$C$15,G4,0)),$E$4:$E$15))

Using AVERAGE and MATCH

Here, the MATCH function will match the values for the selected G4 cell from the Location column then pass the value to ISNUMBER. Then the IF function will apply the logical_test in the cell 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.

Using AVERAGE and MATCH

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

Using AVERAGE and MATCH

5. Using AVERAGE & VLOOKUP

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 the cell H4
Then, type the following formula in the selected cell or into the Formula Bar.

=AVERAGE(VLOOKUP(G4,$B$4:$E$8,{2,3,4},0))

Using AVERAGE & VLOOKUP

Here, the VLOOKUP function will fetch the values for the selected G4 cell from the Location column for the selected cell 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.

Using AVERAGE & VLOOKUP

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

Using AVERAGE & VLOOKUP

6. Using SUMIF & COUNTIF

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

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

=SUMIF($C$4:$C$15,G4,$E$4:$E$15)/COUNTIF(C4:C15,G4)

Using SUMIF & COUNTIF

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.

Using SUMIF & COUNTIF

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

Using SUMIF and COUNTIF

Practice Section

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.

Practice Sheet

 

Practice Sheet

Conclusion

In this article, I tried to explain 6 easy and quick ways of how 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, and feedback please feel free to comment down below.


Further Readings

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo