Forum Discussion

HansVogelaar's avatar
HansVogelaar
MVP
Dec 03, 2021

Rai_Sahab 

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

 

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

Rai_Sahab's avatar
Rai_Sahab
Copper Contributor
Dec 03, 2021
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
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Dec 03, 2021

    Rai_Sahab 

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

    • Detlef_Lewin's avatar
      Detlef_Lewin
      Silver Contributor
      Dec 03, 2021

      HansVogelaar 

      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)

       

       

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Dec 03, 2021

        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.

Resources