In Excel,Is there a way to to use "index match match" to find a value on multiple worksheets, then have the value from the worksheet desired pasted?

Question

I have monthly reports that are spread across various worksheets where I would use "index match match" to find the necessary value on these monthly reports to display the value on a main worksheet (basically a dashboard). I was wondering if there was a way to make it so that I can choose/select which month value shows on the dashboard (maybe by using a combo box?, just not to familiar with how I would do that).

For Reference of Main(dashboard) Here is where I would put the "index match match", looking to be able to worksheet the "index match match) is referencing

For Reference of One of the Months Here is where I would get the data for one month.

For Example:

I would need to be able to switch between having the result displayed on the main worksheet across Canada Value, Mexico Value, and USA Value from

=INDEX('month 1'!$A:$ZZ,MATCH("EM - Easy Money",'month 1'!$A$1:$A$100,0),MATCH("Canada",'Month 1'!$A$1:$ZZ$1,0))

to

=INDEX('month 2'!$A:$ZZ,MATCH("EM - Easy Money",'month 2'!$A$1:$A$100,0),MATCH("Canada",'Month 2'!$A$1:$ZZ$1,0))

Any help would be greatly appreciated!

Two possibilities come to mind, one is to use a dropdown box to select, the other is to use indirect() or may be both together. — Solar Mike, Aug 05 '21 at 23:06
Hello, would you happen to have an example of how to do this? @SolarMike — ace ace, Aug 05 '21 at 23:14
Check on here. I’m not writing one as I am on a smartphone, I thought giving you a hint would help... And Excel has a help file so check that for an example of indirect(). — Solar Mike, Aug 05 '21 at 23:30
When someone gives you a suggestion for a solution, the first thing you should do is go to Google or your favorite search engine and do some research about the topic, instead of immediately asking for a coded solution. We expect you to put some effort into research before posting here. — Ken White, Aug 06 '21 at 01:43
Indirect formula suggested by @SolarMike works. For example, refer SO question [Dynamic worksheet name in Index/Match formula](https://stackoverflow.com/a/61545265/9808063) and [this](https://stackoverflow.com/a/60893874/9808063) — Naresh, Aug 06 '21 at 06:00
If your dropdown is in A1 of the main sheet then try `=INDEX(INDIRECT("'"&A1&"'!$A$1:$ZZ$100"),MATCH("EM - Easy Money",INDIRECT("'"&A1&"'!$A$1:$A$100"),0),MATCH("Canada",INDIRECT("'"&A1&"'!$A$1:$ZZ$1"),0))` — Naresh, Aug 06 '21 at 06:14

score 0 · Answer 1 · answered Aug 08 '21 at 01:17

You could try to use indirect, index/match, or some other formulas to try to grab this data but these are going to require you to update the formula monthly and are very likely to break at some point.

I would recommend reformatting your data a bit and using a pivot table with a slicer (or a timeline). I went ahead and reformatted your example (see pictures). The nice thing about this setup is you won't have to add new sheets every month and you can update the Main sheet by clicking Data-> Refresh All.

Please let me know if you would like more help setting this up!

In Excel,Is there a way to to use "index match match" to find a value on multiple worksheets, then have the value from the worksheet desired pasted?

1 Answers1