+ Reply to Thread
Results 1 to 8 of 8

Using VLOOKUP to find certain values and Get their AVERAGE

  1. #1
    Registered User
    Join Date
    11-29-2016
    Location
    North Carolina
    MS-Off Ver
    2010
    Posts
    15

    Question Using VLOOKUP to find certain values and Get their AVERAGE

    I am trying to use VLOOKUP to grab the number of units sold based on the average cost. But, the issue is that there are multiple weeks that show movement at this same cost. I need to average them all together and show that number in the selected cell.

    So far I have:
    =VLOOKUP(D3 & AU3,Data!$A$2:$AJ$155894,32,FALSE)

    I am using 2 different criteria to get the Average cost already is why I have the D3 & AU3.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Using VLOOKUP to find certain values and Get their AVERAGE

    Excel has an AVERAGEIF formula. If you need more than one criteria you can use SUMIFS() and COUNTIFS().

    Please attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    09-09-2013
    Location
    Iran
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: Using VLOOKUP to find certain values and Get their AVERAGE

    Hi
    You've missed this one:
    AVERAGEIFS (for more than one condition)
    Islam = Peace
    Shia = Peace
    Iran = Peace

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Using VLOOKUP to find certain values and Get their AVERAGE

    NeckRoFeltYa welcome to the forum

    if the above suggestions dont work for you, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    11-29-2016
    Location
    North Carolina
    MS-Off Ver
    2010
    Posts
    15

    Cool Re: Using VLOOKUP to find certain values and Get their AVERAGE

    AVERAGEIFS worked great! I had never used any of the IFS and was searching for a VLOOKUP to help me do it.

    Here is the formula in its glory:
    =AVERAGEIFS(Data!$AF$2:$AF$155894,Data!$B$2:$B$155894,'2017'!D3,Data!$AE$2:$AE$155894,'2017'!BG3)
    Last edited by NeckRoFeltYa; 11-29-2016 at 05:08 PM.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Using VLOOKUP to find certain values and Get their AVERAGE

    Quote Originally Posted by NeckRoFeltYa View Post
    Got it! I took the SUMIFS/COUNTIFS giving me the average. Thanks dflak!
    Then probably averageifS() will do that too?

  7. #7
    Registered User
    Join Date
    11-29-2016
    Location
    North Carolina
    MS-Off Ver
    2010
    Posts
    15

    Thumbs up Re: Using VLOOKUP to find certain values and Get their AVERAGE

    Quote Originally Posted by FDibbins View Post
    Then probably averageifS() will do that too?
    For some reason I googled SUMIFS by accident for the criteria. But switched it to AVERAGEIFS after I looked at the post again! Thanks for noticing as well!

    From now on I will upload a small spreadsheet of what I am working with.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Using VLOOKUP to find certain values and Get their AVERAGE

    Good stuff, im happy you got what you needed

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] find the average of the first 3 non zero values of a row
    By gutterball in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-12-2015, 03:13 AM
  2. [SOLVED] Use vlookup to find the dates and then average the values in corresponding column
    By ash369ster in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 07-03-2015, 12:09 AM
  3. Replies: 13
    Last Post: 10-08-2014, 08:35 AM
  4. [SOLVED] Find average values with conditions
    By bigband1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-14-2012, 05:28 PM
  5. Find the average of the lowest four values of the last six
    By pjmcc64 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-03-2010, 06:26 PM
  6. Replies: 14
    Last Post: 04-02-2010, 02:22 AM
  7. Average for range of values (VLOOKUP?)
    By Poolio in forum Excel General
    Replies: 1
    Last Post: 01-09-2007, 08:20 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1