New Contributor

# How to use vlookup with average function

How to Use VLOOKUP to return the Average Salary of Sales Department.
9 Replies

# Re: How to use vlookup with average function

It makes no sense to use VLOOKUP for that. Instead, use

=AVERAGEIF(B2:B55,F2,C2:C55)

# Re: How to use vlookup with average function

Why to use VLOOKUP() if you can do that with AVRERAGEIF() ?

# Re: How to use vlookup with average function

Even I thought that would have been much logical but the question in this exercise asked to use one function of vlookup to find average salary of sales department.
Is there any way we can get the correct answer using vlookup

# Re: How to use vlookup with average function

As because the exercise require to solve it using that. @sergei

# Re: How to use vlookup with average function

Sorry, have no idea how to do that

# Re: How to use vlookup with average function

VLOOKUP is not a suitable function for calculating an average. The exercise makes no sense.

# Re: How to use vlookup with average function

You could do a crazy workaround.

In E13 type Sales.

In F13:

``=AVERAGIFS(\$C\$2:\$C\$55,\$B\$2:\$B\$55,E13)``

In F3:

``=VLOOKUP(F2,E13:F13,2,FALSE)``

# Re: How to use vlookup with average function

That way, you can use VLOOKUP in any formula in Excel! One could create this formula

``=N(SUM(PRODUCT(VLOOKUP(F2,E13:F13,2,FALSE))))``

and claim that all these functions were used to return the average.

By the way, your formula has a point instead of a comma.

# Re: How to use vlookup with average function

I think you are all reading too much into this problem. This is some class exercise and I'm willing to bet the teacher just wants them to use VLOOKUP to pull/filter the original list into a new list so they can easily use AVERAGE to find the average salary. The exercise might have been created years ago before AVERAGEIF was introduced or maybe the teacher just hasn't introduced it yet or just needed some reason for them to use VLOOKUP to pull the list.
That said I don't care to do homework for others as they don't learn well that way, but I hope that helps you @Rai_Sahab understand what I think the teacher wants you to do. i.e. use VLOOKUP to create rows on the right side for only the Sales Department and then have a cell to calculate the AVERAGE.