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