Results 1 to 4 of 4

Thread: Help Needed with VLOOKUP Function to Retrieve Data from Multiple Sheets - Emmanuel Katto Uganda

  1. #1
    Junior Member
    Join Date
    Aug 2024
    Posts
    1
    Rep Power
    0

    Help Needed with VLOOKUP Function to Retrieve Data from Multiple Sheets - Emmanuel Katto Uganda

    Hello ExcelFox Community,

    I hope you're all doing great! I’m Emmanuel Katto, and I’m currently tackling some data analysis in Excel. I’m reaching out to seek your advice on an issue I’m facing with the VLOOKUP function while trying to retrieve data from multiple sheets.

    The Scenario:
    I have a workbook with three sheets named "Sales2021," "Sales2022," and "Sales2023", each containing the following columns:
    Product ID Product Name Sales Amount
    001 Apples 150
    002 Oranges 200
    003 Bananas 180


    I have another sheet named "Summary" where I want to compile the sales amounts for products across these three years.

    What I Want to Achieve:
    In the "Summary" sheet, I’d like to create a VLOOKUP formula that pulls the sales amount for each product for all three years into a single row, like this:
    Product ID Product Name Sales 2021 Sales 2022 Sales 2023
    001 Apples
    002 Oranges
    003 Bananas

    What I’ve Tried:
    I initially tried using VLOOKUP directly referring to each sheet, but I ran into issues when I needed to adjust references and ensure it searches on multiple sheets correctly. Here’s an example of what I tried for Sales 2021:


    =VLOOKUP(A2, Sales2021!A:C, 3, FALSE)



    While this works for one sheet, I'm unsure how to apply this across multiple sheets in the "Summary" without creating a complicated mess of nested formulas.

    Is there a more effective way to pull data from multiple sheets using VLOOKUP, or would you recommend a different function (like INDEX/MATCH)?
    Can someone provide a step-by-step approach on how to set this up in the "Summary" sheet?

    Thank you so much for your help! I’m eager to learn and appreciate any insights you can provide.

    Best regards,
    Emmanuel Katto

  2. #2
    Sir Moderator sandy666's Avatar
    Join Date
    May 2018
    Posts
    238
    Rep Power
    7

    Cool

    I suggest to use Power Query (What is Power Query)
    sandy
    I know you know but I forgot my Crystal Ball and don't know what you know also I may not remember what I did weeks, months or years ago so answer asap. Thinking doesn't hurt

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Just use this formula

    Code:
    =SUM(SUMIF(INDIRECT("'"&$H$1:$H$4&"'!A2:A99"), A2, INDIRECT("'"&$H$1:$H$4&"'!C2:C99")))
    on your master sheet, where H1:H4 consists the sheet names Sales2021, Sales2022, Sales2023, Sales2024 etc. And change the 99 to a larger row number if you have lots of rows in your indidual sheets.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  4. #4
    Junior Member
    Join Date
    Aug 2024
    Posts
    7
    Rep Power
    0
    Hey Emmanuel,

    Instead of VLOOKUP, try using INDEX and MATCH for more flexibility across sheets. For Sales 2021, you can use:

    =INDEX(Sales2021!C:C, MATCH($A2, Sales2021!$A:$A, 0))

    For Sales 2022 and Sales 2023, just change the sheet names accordingly. This approach avoids the complexity of using multiple VLOOKUPs.

Similar Threads

  1. Replies: 9
    Last Post: 02-28-2018, 01:23 AM
  2. Replies: 0
    Last Post: 07-05-2017, 09:28 PM
  3. Lookup Data From Multiple Sheets
    By mahmoud-lee in forum Excel Help
    Replies: 6
    Last Post: 01-28-2014, 10:22 PM
  4. Vlookup help needed
    By AbuReem in forum Excel Help
    Replies: 15
    Last Post: 11-12-2013, 11:32 AM
  5. Vlookup - Multiple Sheets
    By Suhail in forum Excel Help
    Replies: 3
    Last Post: 01-30-2013, 06:47 PM

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
  •