+ Reply to Thread
Results 1 to 7 of 7

Average Of Range - Based on VLOOKUP

  1. #1
    Registered User
    Join Date
    08-09-2017
    Location
    Wyoming, MI
    MS-Off Ver
    2017
    Posts
    3

    Average Of Range - Based on VLOOKUP

    Ok, so I'm back for some more.

    This time I'm looking to take the Average of a range of cells, based on a vlookup from a separate table/sheet.
    Table2.png

    In the image, for cell C5 and down, I want to VLOOKUP the value of the corresponding column D reference against Table 2 and take the average of the row that the VLOOKUP finds from column H through K.

    Any ideas oh guru's of Excel?

  2. #2
    Registered User
    Join Date
    09-12-2008
    Location
    Riversul
    MS-Off Ver
    2016
    Posts
    83

    Re: Average Of Range - Based on VLOOKUP

    Howdy, I'm no expert, but maybe something like that?

    Please Login or Register  to view this content.
    Or maybe could simply create another column with that average formula, and use a single VLOOKUP to find it? Anyway, I hope that I could help you ~

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Average Of Range - Based on VLOOKUP

    OK TBB,

    Try this using a CSE entry.
    =AVERAGE(VLOOKUP(D5,Weekly!$G$5:$K$8,{2,3,4,5},FALSE))

    See the attached.
    Average using CSE and Array.xlsx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Average Of Range - Based on VLOOKUP

    Or without CSE

    =AVERAGE(INDEX(Weekly!$H$5:$K$8,MATCH(D5,Weekly!$G$5:$G$8,0),0))

  5. #5
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,892

    Re: Average Of Range - Based on VLOOKUP

    Or:

    =AVERAGE(INDEX(Weekly!$H$5:$K$8,MATCH(D5,Weekly!$G$5:$G$8,0),))

    Normal Enter.

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Average Of Range - Based on VLOOKUP

    Hey Jonmo and Phuocam,

    Your formulas are very close. Do you need that last zero in Jonmo's formula or not?

  7. #7
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Average Of Range - Based on VLOOKUP

    Regular formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

+ 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. Vlookup for Max / Min / Average Value in Range
    By Metcalfe in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-19-2017, 07:22 AM
  2. [SOLVED] average over vlookup range?
    By erk_raven in forum Excel General
    Replies: 13
    Last Post: 11-12-2012, 10:02 AM
  3. Replies: 6
    Last Post: 09-04-2012, 10:35 AM
  4. Average over a text range using a vlookup In Column 2nd
    By Kenneth1024 in forum Excel General
    Replies: 6
    Last Post: 09-10-2010, 08:08 AM
  5. Re: Average over a text range using a vlookup In Column
    By Kenneth1024 in forum Excel General
    Replies: 4
    Last Post: 09-08-2010, 02:38 AM
  6. Average over a text range using a vlookup
    By hemd in forum Excel General
    Replies: 2
    Last Post: 10-22-2009, 05:38 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