+ Reply to Thread
Results 1 to 3 of 3

Vlookup from multiple sheets to get an average

  1. #1
    Registered User
    Join Date
    North Carolina
    MS-Off Ver

    Vlookup from multiple sheets to get an average

    I have been tasked with maintaining a performance file, inside of it are sheets for each week of the month (12.30-1.5) (1.6-1.12) etc and this information will be manually entered because I haven't figured out how to Vlookup when data is not in the same position on multiple sheets and if I am honest, I am not the best user of Vlookup. Anyway, we have sheets for each week and then a sheet called MTD. My question is how do I use a Vlookup on the MTD sheet to look up and average the data in the weekly sheets? There are multiple topics within each sheet, quality average, absenteeism, adherence etc. So the vlookup would need to be specific to each category, pulling from each weekly sheet, to average on the monthly sheet to get the MTD stats.

    I have no idea if I have formatted this correctly and I really do appreciate everyone's help. I am not the best user of Excel but before I ask I make sure to try to find the answer myself but for this one I am lost. If you need additional information, please let me know.

  2. #2
    Registered User
    Join Date
    Fort Lauderdale
    MS-Off Ver
    Excel 2018

    Re: Vlookup from multiple sheets to get an average

    I am giving you good advice here, make your life so much easier and just take the time to re-organize each sheet so that the data rows and columns are consistent throughout and then keep it this way going forward. If you receive information from others, then provide them with a template so they keep to this format as well.

    Then use Pivot Tables (YouTube has tons of help guides) to consolidate and create the reporting, including averages, that you want.

    I highly recommend what I describe above, that is much easier and safer. Having said this, if you only have a few cells to average, then vlookup and/or offset functions could be used. I am new here and so I cannot post links to help you; however, just search for "vlookup vs offset" and "vlookup vs offset match index" and you will find what you are seeking. Do this in YouTube as well.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16

    Re: Vlookup from multiple sheets to get an average

    Welcome to the forum

    I would offer different advice. It is standard practice to try and keep all data on 1 sheet, and then use other sheets for summaries/analysis.
    I agree that standard layout of data is vital to simplify calculations.

    PT may be the way to go, but if you standardize the layout of your data, vlookup or INDEX/MATCH may be a better option.

    Without seeing some sample data (and expected outcome), it is almost impossible to give any more specific advice.

    upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    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


+ 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. Average with vlookup of multiple sheets
    By zanael in forum Excel General
    Replies: 3
    Last Post: 01-07-2016, 11:49 AM
  2. [SOLVED] Look up and average multiple values from multiple sheets
    By bcas77 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-15-2015, 05:38 PM
  3. Average multiple values from multiple sheets
    By xptandre in forum Excel General
    Replies: 2
    Last Post: 11-14-2014, 07:39 PM
  4. Multiple Sheets Multiple Criteria Average Data
    By apauaie in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-17-2013, 01:13 PM
  5. Average VLookup over multiple sheets
    By pjsilber in forum Excel General
    Replies: 3
    Last Post: 10-05-2011, 07:15 PM
  6. Average columns across multiple sheets
    By Stayfair in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-10-2011, 09:10 PM
  7. Average over multiple Sheets with DIV/0 Error
    By hund in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-26-2007, 12:45 AM

Tags for this Thread


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