Index/Match VLookup Multiple Criteria Across Multiple Tabs

ProfessionalWimp

New Member
Joined
Sep 3, 2016
Messages
8
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!
 
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!
 
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top