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

I suggest to use Power Query (What is Power Query)

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.

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.