# INDEX MATCH and INDIRECT to pull in data from multiple worksheets

1. ## INDEX MATCH and INDIRECT to pull in data from multiple worksheets

Good afternoon!

I've been searching for several hours but can't seem to find the exact answer that will work for me. I have a workbook that contains yearly forecasts for various categories of products -- each is contained in a separate tab (22 worksheets in total). All worksheets are structured in the same fashion - data ranges have been made the same.

I have a summary tab where I would like to pull in these final forecasts by product number and year.

I've been able to pull in the results if I know what sheet the data is on via the following:

=INDEX(Toys!\$Y\$5:\$AE\$150,MATCH(\$A24,Toys!\$A\$5:\$A\$150,0),MATCH(H\$5,Toys!\$Y\$2:\$AE\$2,0))

What I would like to do is have the formula look through the tabs so I do not need to specify where to look for each product. I think this means I need to use the INDIRECT function. I've created a list of the 22 worksheet names in in AZ2:AZ23 of the summary tab. I've tried various versions of the below but can't seem to get it too work.

=INDEX(INDIRECT("'"&\$AZ\$2:\$AZ\$23&"'!"&"\$Y\$5:\$AE\$150"),MATCH(\$A24,INDIRECT("'"&\$AZ\$2:\$AZ\$23&"'!"&"\$A\$5:\$A\$150"),0),MATCH(H\$5,INDIRECT("'"&\$AZ\$2:\$AZ\$23&"'!"&"\$Y\$2:\$AE\$2"),0))

Any thoughts on what I'm missing or is there a simpler way?

2. ## Re: INDEX MATCH and INDIRECT to pull in data from multiple worksheets

Welcome to the forum. Post a sample workbook and we can help.

3. ## Re: INDEX MATCH and INDIRECT to pull in data from multiple worksheets

Pivot Table with multiple "sources".

http://office.microsoft.com/en-001/e...010226585.aspx

4. ## Re: INDEX MATCH and INDIRECT to pull in data from multiple worksheets

If it were me, I wouldnt bother creating some slick complex indirect function, I would aggregate the data and then have ONE pivot off ONE source. It makes things so much easier.

5. ## Re: INDEX MATCH and INDIRECT to pull in data from multiple worksheets

Can you explain what you mean by aggregate and then pivot off one?

6. ## Re: INDEX MATCH and INDIRECT to pull in data from multiple worksheets

He means copy all the separate sheets into one sheet to serve as a database, from there it is breezy to create a Pivot Table or Pivot Report or Pivot Chart off the single data set.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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