How to use vlookup with average function

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

@Rai_Sahab 

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

 

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

@Rai_Sahab 

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

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
As because the exercise require to solve it using that. @sergei

@Rai_Sahab 

Sorry, have no idea how to do that

@Rai_Sahab 

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

@Hans Vogelaar 

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)

 

 

@Detlef Lewin 

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.

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.