Index/Match VLookup Multiple Criteria Across Multiple Tabs

[ProfessionalWimp]

Hi Everyone -

I am stumped on building this formula out to incorporate multiple criteria across multiple tabs. I have spent hours searching for a similar problem and I cannot find anything that works. I hope someone can help.

Currently, I have 2 criteria and only 2 tabs to look through, BUT when this workbook is done, it will have maybe 50 tabs to search for both criteria. Here are the definitions:

A2 = dropdown with criteria 1 (name of tab).
C2 = Criteria 2 hard coded (month).
I have named a SheetList which lists all of my tabs. Nested "if" will not work due to the volume I will have when this is done.

Example) If my dropdown in A2 is WATER and my month is JAN, I want the formula to search ALL tabs, find BEAR tab and give me the numbers in the JAN slot.
Sample table of result tab:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Product Code (A)[/TD]
[TD](B)[/TD]
[TD]Month (C)[/TD]
[TD]Result Column (D)
(Gallons)
[/TD]
[TD](E,F,G,H)
Columns[/TD]
[/TR]
[TR]
[TD]WATER tab (dropdown)[/TD]
[TD][/TD]
[TD]JAN[/TD]
[TD] 79,668[/TD]
[TD]other years[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]FEB[/TD]
[TD] 63,060[/TD]
[TD]sets of data[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]MAR[/TD]
[TD] 78,672[/TD]
[TD]hard numbers[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]APR[/TD]
[TD] 75,816[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
For the search areas - range is $A$2:$H$14 on all tabs - all tabs are formatted exactly the same. Column A is the months of the year. Column B:H are hard typed numbers. A2 has the tab name hard typed in.
The return result is simply the cell contents from the appropriate tab selected in the drop down from the appropriate month. See red numbers.

Sample table of tab to search - all tables match format.
[TABLE="class: grid, width: 387, align: left"]
<tbody>[TR]
[TD] WATER tab (A)[/TD]
[TD]2014 (B)[/TD]
[TD]2015 (C)[/TD]
[TD]2016 (D)[/TD]
[/TR]
[TR]
[TD]JAN[/TD]
[TD]79,668[/TD]
[TD]46,212[/TD]
[TD]79,092[/TD]
[/TR]
[TR]
[TD]FEB[/TD]
[TD]63,060[/TD]
[TD]73,236[/TD]
[TD]91,044[/TD]
[/TR]
[TR]
[TD]MAR[/TD]
[TD]78,672[/TD]
[TD]123,504[/TD]
[TD]101,640[/TD]
[/TR]
[TR]
[TD]APR[/TD]
[TD]75,816[/TD]
[TD]53,940[/TD]
[TD]71,064[/TD]
[/TR]
</tbody>[/TABLE]

My current formula is an array and it only considers criteria 2 (C2) (the month) I need it to consider C2 and A2:
{VLOOKUP(C2,INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$A$2:$h$14"),C2)>0),0))&"'!$A$2:$h$14"),2,0)}

I have tried to incorporate the multiple variations of:
*(A2:H14=A2) inserting this in various areas of the array formula to no luck
&A2 trying to use the & method in various areas as well with no luck

I saw this sample formula in my extensive searchings but I cannot get this to return a result:
VLOOKUP(C2,INDIRECT(INDEX(Sheets&"$A$2:$H$14",MATCH(1,COUNTIFS(INDIRECT(Sheets&"$A$2:$H$14"),C2,INDIRECT(Sheets&"$A$2:$H$14"),A2),0))),2,0)

Thing is, I can get it to work if everything was on the same worksheet, but that is not going to be a possibility. I have also tried naming my ranges to no avail either. I hope someone has done this before and can help.

Thanks!

 

[ProfessionalWimp]

In order not to interfere with Howard's solution and to avoid tons of explaining and confusion, I would like you to play with this workbook
https://www.dropbox.com/s/es1p1u1w88xk6wk/976569-index-match-vlookup.xlsm?dl=0

I appreciate your comments.

This is perfect!! I don't know why I didn't think to use a pivot table to assist in the matter! I feel a big fat 'doh' there... Thank you so very much for the hard example. It is EXACTLY what I am looking for!! Sorry for the delay in response - Thanksgiving week and crazy busy with long hours. Again, many thanks for this help!