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

**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.**

*Sales.***Table of Contents**hide

## Download to Practice

## 6 Ways to Calculate VLOOKUP AVERAGE in Excel

**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**.

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

Here, from the ** Sales **and

**column, this function will look up the average sales value and will return the**

*Sales Person***name.**

*Sales Person*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.

**2. Utilize AVERAGEIF Function to Find VLOOKUP AVERAGE in Excel**

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)`

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

**column as an**

*Sales***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.

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 to Get VLOOKUP AVERAGE 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 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))`

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.

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

**4. Join AVERAGE and MATCH Functions to Count VLOOKUP AVERAGE in Excel**

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 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))`

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 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**.

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.**

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

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**.

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 to Display VLOOKUP AVERAGE in Excel**

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 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)`

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.

**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**. You will find similar problem-solving articles on our** ExcelDemy** website. Last but not least, if you have any kind of suggestions, ideas, and feedback please feel free to comment down below.