Two Dimensional Lookup with Match and Index for multiple columns

[chipsworld]

Help! I have been working on this for hours now, and having no luck...

I am trying to allow the user to select an Excel the source workbook, and perform a Two Dimensional lookup to pull that data into a target workbook.

There are 5 columns to import data to. The source sheet is "Exactly" the same as the target. I just need to import the data as there will be multiple source files that all match to different parts of the whole.

All lookup values are in Column A and all data to import is in Column c5 thru i234

Also...Once I get this part resolved, I need to figure out to import additional data for the same cells, and add the new numbers to the existing.

All Data points are numeric...

Private Sub cmdimport_Click()
Dim filter As String
Dim caption As String
Dim SourceF As String
Dim SourceW As Workbook
Dim TargetW As Workbook
Dim SourceS As Worksheet
Dim TargetS As Worksheet



On Error Resume Next

' make weak assumption that active workbook is the target
Set TargetW = Application.ThisWorkbook

' get the customer workbook
filter = "*.xl* (*.xls*),*.xls*"
caption = "Please Select file to import "
SourceF = Application.GetOpenFilename(filter, , caption)

Set SourceW = Application.Workbooks.Open(SourceF)

Set SourceS = SourceW.Worksheets("AAR")

Dim LastRow As Long
Dim r As Long
    
    With SourceS
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        
        For r = 5 To LastRow Step 1
        'apply the two dimensional lookup formula using INDEX and MATCH
        TargetW.Sheets("AAR").Cells(r, 3, 9).Value = Application.WorksheetFunction.Index(SourceS.Range("B5:I234"), _
        Application.WorksheetFunction.Match(TargetW.Sheets("AAR").Cells(r, 3, 9), SourceS.Range("A5:I234"), 0), _
        Application.WorksheetFunction.Match(TargetW.Sheets("AAR").Cells(r, 3, 9), TargetW.Sheets("AAR").Range("C2:I2"), 0))
        
        Next r
        
    End With

End Sub

 

[GlennUK]

Jeez, will you explain your intended logic, 'coz I can't work out what you're aiming for from that code? It's this that makes no sense whatsoever:

        TargetW.Sheets("AAR").Cells(r, 3, 9).Value = Application.WorksheetFunction.Index(SourceS.Range("B5:I234"), _
        Application.WorksheetFunction.Match(TargetW.Sheets("AAR").Cells(r, 3, 9), SourceS.Range("A5:I234"), 0), _
        Application.WorksheetFunction.Match(TargetW.Sheets("AAR").Cells(r, 3, 9), TargetW.Sheets("AAR").Range("C2:I2"), 0))

... being that you seem to using the cell being filled as a lookup value (bad enough), but also using the same value for vertical and horizontal lookups (bizarre), with a row search into a rectangular area (not the indexed area, the SEARCH area!?!?!).

 

[chipsworld]

Glenn,
I do apologize if it is hard to follow... I will try to make my explanation more simple.

I have a spreadsheet with 229 rows x 5 columns of data ("C" thru "I") that I want to import into another sheet with the exact same layout ("C" thru "I").

The sheets being imported are not all the entire list of line items. they are portions of a grand report (all numeric)

This report is a weekly report that multiple people provide (their portion of the whole). So, instead of copy pasting 30 different portions, the idea is to facilitate an automated process to save both time and money.

The portion of the code that you so eloquently beat up, is my attempt to use Match and Index to perform that import.

This part " TargetW.Sheets("AAR").Cells(r, 3, 9).Value " was to establish the row and column range where the data being brought in will go. Again... the same here " Application.WorksheetFunction.Match(TargetW.Sheets("AAR").Cells(r, 3, 9),"
the "Lookup" value is located in column A on both sheets, and the data on the source sheet is located in columns "C" thru "I", and will be imported to those same columns on the consolidated report.

If I was as good at this as you evidently are, I wouldn't be asking for help from this group...

The idea here is to learn from others and accomplish a task simultaneously.

If you have some input, feel free to share so that I and others can learn from your experience.

 

[GlennUK]

Hi,

there are problems I'm still trying to get my head around (logic problems, and programming) ... like:
"I have a spreadsheet with 229 rows x 5 columns of data ("C" thru "I")"
Query: C through I is 7 columns (C,D,E,F,G,H.I) ... either the range is wrong, or the count is wrong. Which is it, please?

The line of code for the index match has these issues (some issues are not reported multiple times, even though they exist multiple times in the code):

 

[chipsworld]

Glenn,
Yes...you are right. It is 7. Basically...Saturday through Friday. 229 x7


1. The reference (r, 3, 9) is my attempt tp include all 7 columns. Wasn't sure how to loop through for all 7 columns.

2. Ref (2&3) arg 1 - was supposed to be the search field where source data is located.

3. Ref (2&3)arg 2 - was supposed to be the horizontal lookup values? (Column A

4. ref (4&5) arg 1 - was supposed to be the cells to fill with the data found (Have figured out that it was wrong. r - row, 3,9 are column numbers)

5. ref (4&5)arg 2 - C2:I2 is the lookup value for the vertical lookup

This is my first time trying to use the Match and Index, so have been trying to figure it out looking at others work. Apparently I have done a terrible job of it!
Both sheets (Source and target) are identical in every way. Column "A" has the row lookup values and C2:I2 have the column lookups.

I am trying to simply populate all 7 columns with the data from other sheets as they line up to the lookup values in column A.

Since the input (source) sheets are multiple, and data will need to be summed up with each input (i.e. each source sheet will be 9 columns wide) and need to sum up for each day, I am working on how I can include this in this process.

Thanks! Hope this helps better explain my end goal...

Here is a sample snap of the target form. The source form look exactly the same

 

[chipsworld]

Glenn,
OK...I starting to understand this (Sort of). I have managed to get it to lookup generic data for all columns, but is there an easier (More functional) way to accomplish the same task?

Then, How would I be able to bring in additional data and add (SUM) it with what is already there?

Dim LastRow As Long
Dim r As Long
    
    With SourceS
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        
        For r = 5 To LastRow Step 1
        
        'apply the two dimensional lookup formula using INDEX and MATCH
        TargetW.Sheets("AAR").Cells(r, 3).Value = Application.WorksheetFunction.Index(SourceS.Range("C5:I234"), _
        Application.WorksheetFunction.Match(TargetW.Sheets("AAR").Range("A5"), SourceS.Range("A5:A234"), 0), _
        Application.WorksheetFunction.Match(TargetW.Sheets("AAR").Range("C2"), SourceS.Range("C2:I2"), 0))
        
        TargetW.Sheets("AAR").Cells(r, 4).Value = Application.WorksheetFunction.Index(SourceS.Range("C5:I234"), _
        Application.WorksheetFunction.Match(TargetW.Sheets("AAR").Range("A5"), SourceS.Range("A5:A234"), 0), _
        Application.WorksheetFunction.Match(TargetW.Sheets("AAR").Range("D2"), SourceS.Range("C2:I2"), 0))
        
        TargetW.Sheets("AAR").Cells(r, 5).Value = Application.WorksheetFunction.Index(SourceS.Range("C5:I234"), _
        Application.WorksheetFunction.Match(TargetW.Sheets("AAR").Range("A5"), SourceS.Range("A5:A234"), 0), _
        Application.WorksheetFunction.Match(TargetW.Sheets("AAR").Range("E2"), SourceS.Range("C2:I2"), 0))
        
        TargetW.Sheets("AAR").Cells(r, 6).Value = Application.WorksheetFunction.Index(SourceS.Range("C5:I234"), _
        Application.WorksheetFunction.Match(TargetW.Sheets("AAR").Range("A5"), SourceS.Range("A5:A234"), 0), _
        Application.WorksheetFunction.Match(TargetW.Sheets("AAR").Range("F2"), SourceS.Range("C2:I2"), 0))
        
        TargetW.Sheets("AAR").Cells(r, 7).Value = Application.WorksheetFunction.Index(SourceS.Range("C5:I234"), _
        Application.WorksheetFunction.Match(TargetW.Sheets("AAR").Range("A5"), SourceS.Range("A5:A234"), 0), _
        Application.WorksheetFunction.Match(TargetW.Sheets("AAR").Range("G2"), SourceS.Range("C2:I2"), 0))
        
        TargetW.Sheets("AAR").Cells(r, 8).Value = Application.WorksheetFunction.Index(SourceS.Range("C5:I234"), _
        Application.WorksheetFunction.Match(TargetW.Sheets("AAR").Range("A5"), SourceS.Range("A5:A234"), 0), _
        Application.WorksheetFunction.Match(TargetW.Sheets("AAR").Range("H2"), SourceS.Range("C2:I2"), 0))
        
        TargetW.Sheets("AAR").Cells(r, 9).Value = Application.WorksheetFunction.Index(SourceS.Range("C5:I234"), _
        Application.WorksheetFunction.Match(TargetW.Sheets("AAR").Range("A5"), SourceS.Range("A5:A234"), 0), _
        Application.WorksheetFunction.Match(TargetW.Sheets("AAR").Range("I2"), SourceS.Range("C2:I2"), 0))
        
        Next r
        
    End With

 

[chipsworld]

Glenn,
OK...not getting it after all. How can I get it to go down the page and select the next row lookup value in column A. Says it doesn't support using Cells(r, 1) as a range item.

When I use - Application.WorksheetFunction.Match(TargetW.Sheets("AAR").Cells(r, 1), SourceS.Range("A5:A234"), 0), _ I get an error 1004 : Unable to get the Match property of the WorksheetFunction class.

I can't need to make 234 * 9 copies of this same code to accomplish this. Must be an easier way...

HELP!

 

[GlennUK]

Look at this example, using Find instead of Index and Match, which finds matching data in another sheet, and copies 7 horizontal cells from the source sheet to a report sheet, and see if that gives you some ideas:

    Dim wsRep As Worksheet, wsSource As Worksheet, c As Range
    Set wsRep = Worksheets("Sheet3")
    Set wsSource = Worksheets("Sheet4")
    For Each c In wsRep.Range("A2:A5")
        Set myres = wsSource.Range("A2:A5").Find(c.Value)
        If Not myres Is Nothing Then
            c.Offset(0, 1).Resize(1, 7).Value = myres.Offset(0, 1).Resize(1, 7).Value
        End If
    Next

 

[chipsworld]

Glenn,
This is a different approach, but it does work. Thanks! The only limitation that I see is if I wanted to use the date reference in the top of the sheet as a guide to allow an expansion of columns over say, a month or so. I wouldn't be copying all 30 days of data at once, so this method wouldn't work, where as the original thought of Match, Index should??

Now I only have one issue left... How do I import multiple data sets into a section and SUM them all up into one?

i.e. - I import a data set for one section of the report. Then I need to import another data set from a different employee that matches the same line items, and sum the two together in the master report?

Below is what I did with your example above...where would I try to work this additional capability into this, or what would you suggest as a plan to achieve it. I had to break the ranges down in to a header choice, as there were duplicates, and it was finding the first in the top to bottom search and bringing those over instead of the correct line.

Private Sub cmdImport_Click()
Dim filter As String
Dim caption As String
Dim SourceF As String
Dim SourceW As Workbook
Dim TargetW As Workbook
Dim SourceS As Worksheet
Dim TargetS As Worksheet



On Error Resume Next

Set TargetW = Application.ThisWorkbook

' get the customer workbook
filter = "*.xl* (*.xls*),*.xls*"
caption = "Please Select file to import "
SourceF = Application.GetOpenFilename(filter, , caption)

Set SourceW = Application.Workbooks.Open(SourceF)

Set TargetS = TargetW.Sheets("AAR")

Set SourceS = SourceW.Worksheets("AAR")

Dim c As Range
Dim Tar As Range
Dim Src As Range


    Set TargetS = ThisWorkbook.Worksheets("AAR")

If cmdSection.Value = "S1 Mobilization" Then
Set Tar = TargetS.Range("A5:A59")
Set Src = SourceS.Range("A5:A59")
ElseIf cmdSection.Value = "S1 Administration" Then
Set Tar = TargetS.Range("A63:A96")
Set Src = SourceS.Range("A63:A96")
ElseIf cmdSection.Value = "S1 DEMOB Individuals" Then
Set Tar = TargetS.Range("A102:A132")
Set Src = SourceS.Range("A102:A132")
ElseIf cmdSection.Value = "S1 DEMOB Units" Then
Set Tar = TargetS.Range("A137:A181")
Set Src = SourceS.Range("A137:A181")
ElseIf cmdSection.Value = "CRC" Then
Set Tar = TargetS.Range("A185:A234")
Set Src = SourceS.Range("A185:A234")
End If

    For Each c In Tar 'getS.Range("A5:A234")
        Set myres = Src.Find(c.Value)
        If Not myres Is Nothing Then
            c.Offset(0, 2).Resize(1, 7).Value = myres.Offset(0, 2).Resize(1, 7).Value
        End If
    Next


ThisWorkbook.Sheets("AAR").Activate = True
SourceW.Close

I have no issues with trying to figure this stuff out, but I am sort of in a new arena with this, so guidance is the key.

Again...thank you for your help in getting me this far...

 

[GlennUK]

I can answer one of your queries, I think, like:

"The only limitation that I see is if I wanted to use the date reference in the top of the sheet as a guide to allow an expansion of columns over say, a month or so. I wouldn't be copying all 30 days of data at once, so this method wouldn't work, where as the original thought of Match, Index should?? "
A: look at the Offset part of the value assignment ... when left of the "=" it is controlling where the values go. In other words, you can work out which columns your data needs to go into, and alter the offset accordingly.

But this:
"How do I import multiple data sets into a section and SUM them all up into one?"
A: I don't know what you mean. But, if you need to summarise on line items, then PivotTable the data afterwards, and get the summing that way, at a guess.