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:

Product Code (A)	(B)	Month (C)	Result Column (D) (Gallons)	(E,F,G,H) Columns
WATER tab (dropdown)		JAN	79,668	other years
		FEB	63,060	sets of data
		MAR	78,672	hard numbers
		APR	75,816

<tbody>
</tbody>

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.

WATER tab (A)	2014 (B)	2015 (C)	2016 (D)
JAN	79,668	46,212	79,092
FEB	63,060	73,236	91,044
MAR	78,672	123,504	101,640
APR	75,816	53,940	71,064

<tbody>
</tbody>

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!

 

[ask2tsp]

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.

Wishfull thinking? (just kidding)

It looks like you want a copy of the tab selected on this sheet.
If you only want to look at the numbers I suggest Right-click the triangle to the left of the tabs and select the name from that list, to quickly navigate to that tab.

If you want a copy of the numbers on this sheet build a macro to do that, instead of a complicated array formula. If you want I can assist you with that.

 

[ProfessionalWimp]

Sorry - I did have a mistype there... I want dropdown A2 Water and month JAN to search all tabs and find WATER JAN and dump my number into the Jan slot.
I can handle code - I've only done it once before with the generous help of a youtube video! I am okay with VB but not sure how to do macros. Any help is appreciated. I know it can be done, I've used workbooks like this before - I just don't have them anymore to see how it was done.
Thanks ask2tsp!

 

[L. Howard]

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:

Product Code (A)	(B)	Month (C)	Result Column (D) (Gallons)	(E,F,G,H) Columns
WATER tab (dropdown)		JAN	79,668	other years
		FEB	63,060	sets of data
		MAR	78,672	hard numbers
		APR	75,816

<tbody>
</tbody>

In this table, are there other drop downs in column A with the same Product Codes? (A2 to A13, Jan to Dec)

Where Water is a selection from drop down in A2 looking for JAN? I assume A4 has a drop down and a Product Code selection of "Product X" there would be looking for MAR on a sheet named Product X, which is 78,672.

Where does Columns E, F, G, H come into play, if at all?

Your typo find BEAR tab should be find WATER tab?

Howard

 

[L. Howard]

Making a few assumptions per my Post #4 AND you are willing to use VBA, try this copied to the sheet module that has the Sample Table of results on it.
Then from any of the drop down in column A make a selection and note the return values to the same row.

Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:A")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

Dim shN As String
Dim LRow As Long
Dim aMon As String
Dim myRng As Range

shN = Target.Value
aMon = Target.Offset(, 2).Value

With Sheets(shN)
   LRow = .Cells(Rows.Count, "A").End(xlUp).Row
   Set myRng = Sheets(shN).Range("A2:A" & LRow).Find(What:=aMon, _
                                                LookIn:=xlValues, _
                                                LookAt:=xlWhole, _
                                                SearchOrder:=xlByRows, _
                                                SearchDirection:=xlNext, _
                                                MatchCase:=False)
                                               
  If Not myRng Is Nothing Then
     'MsgBox "Match for " & aMon & " in cell " & myRng.Address & " " & myRng.Offset(, 1)
     'Target.Offset(, 3) = myRng.Offset(, 1)                 'returns column D
     myRng.Offset(, 1).Resize(1, 7).Copy Target.Offset(, 3) 'returns column D to H
     
    Else
     MsgBox "No match found for - " & aMon
     
  End If
         
End With
End Sub

 

[ProfessionalWimp]

Thank you L Howard. Yes, your assumptions are correct in above and yes, there will only be 1 drop down. I have some commitments tonight, but will work on this tomorrow morning and see how it goes.
I can simply copy and paste into VBA and test from there? As I am willing to try, I freely admit, I do not know how to read code - I am attempting though!

 

[L. Howard]

Okay, I will need to amend the code for only 1 drop down. But it will work for an A2 product code and JAN as it is, I believe.

I'll post a revision later, as soon as I can.

Paste the code, as is, in the sheet module and it will react automatically when you make a change, which would be a selection, in column A.

Howard

 

[ask2tsp]

This approach is adaptive. When you have added one or more sheets the sheetlist is rebuilt on opening the workbook.

When the Workbook opens the sheetlist is populated and sorted and the validation for cel A2 is set (this code goes to the code page of ThisWorkbook)

Option Explicit

Private Sub Workbook_Open()
'Populate Sheetlist & set A2 validation

  Dim sht   As Worksheet
  Dim shtQ  As Worksheet
  Dim r     As Long
  
  Set shtQ = Me.Worksheets("Query")
  shtQ.Range("A5:A200").ClearContents
  r = 4
  For Each sht In Me.Worksheets
    If Not sht Is shtQ Then
      r = r + 1
      shtQ.Cells(r, 1) = sht.Name
    End If
  Next sht
  'Sort the Sheetlist
  shtQ.Sort.SortFields.Clear
  shtQ.Sort.SortFields.Add Key:=Range("A5"), _
      SortOn:=xlSortOnValues, Order:=xlAscending, _
      DataOption:=xlSortNormal
      
  With shtQ.Sort
    .SetRange Range("A5:A" & r)
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
  End With
  
  'Set validation for A2
  With shtQ.Range("A2").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
     Operator:=xlBetween, Formula1:="=$A$5:$A$" & r
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
  End With
End Sub

When a value is selected in A2 the data is copied to this page (this code goes to the code page of the Query Sheetlist)

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
  'If A2 changes get data from that sheet
  Dim sht   As Worksheet
  Dim arr() As Variant
    
  If Target.Address(0, 0) = "A2" Then
    Set sht = ThisWorkbook.Worksheets(Target.Value)
    arr = sht.Range("B1:H13")
    Me.Range("D1:J13") = arr
  End If
End Sub

 

[L. Howard]

Thank you L Howard. Yes, your assumptions are correct in above and yes, there will only be 1 drop down. I have some commitments tonight, but will work on this tomorrow morning and see how it goes.
I can simply copy and paste into VBA and test from there? As I am willing to try, I freely admit, I do not know how to read code - I am attempting though!

Disregard the code in my Post #5. Delete in its entirety. Don't save it for observation. (You cannot have two change macros in the same module)

Copy this to the sheet module on the summary or main sheet.

With the following assumptions.

The product code drop down is in cell A2 on the main sheet.
The months are in Column C, They do not have to be in order and can have blank rows between them.
The months on main sheet and the product sheet MUST be spelled the same.
The product code names are EXACTLY the same as the tab name for the sheet they represent. (Upper/Lower case, spaces, dashes and all.)
The code returns columns B - H from the product sheet to the row of the month selected in the input box, Main sheet, columns D - J.

The code is activated by selecting an item in the drop down in A2, then you get an input box, where you will click on a specific month in Column C. The code finishes the transfer.

Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A2")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

Dim aMon As String
Dim aMonAddress As Range
Dim shN As String
Dim LRow As Long
Dim myRng As Range

shN = Target.Value

Set aMonAddress = Application.InputBox(prompt:="Select a Month in Column C", Title:="C Column Month", Type:=8)

aMon = aMonAddress.Value

'MsgBox aMon
'MsgBox aMonAddress.Address

With Sheets(shN)

   LRow = .Cells(.Rows.Count, "A").End(xlUp).Row

   Set myRng = .Range("A2:A" & LRow).Find(aMon, LookIn:=xlValues)
                                               
  If Not myRng Is Nothing Then
   
       aMonAddress.Offset(, 1).Resize(1, 7).Value = myRng.Offset(, 1).Resize(1, 7).Value
     
    Else
      MsgBox "No match found for - " & aMon
     
  End If
         
End With

End Sub

 

[ask2tsp]

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

Sheet 'Query'
is the sheet where you can select a product and the numbers for thar product will show.

Sheet 'Merged'
is all the sheets combined in one list

Sheet 'Report'
there's a pivot table based on the merged list

So you have two ways to look at the data, the query sheet and the Report sheet.

When you add product sheets they are incorporated when the workbook opens. No need for a seperate list of sheetnames. To update the Merged+Report sheets, run the mergeData macro.

I appreciate your comments.