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

 

[chipsworld]

Glenn,
First Answer...I understand how this works...not a problem. I was just thinking that if my master sheet (Target) was say...30 days long instead of 7, I could use the date as a vertical reference for multiple periods instead of weekly. Just more flexible. Anyway...not that important.

As to my second question on (SUM) issues...

• The Master Sheet (Target) has 5 separate sections, for the 5 sections in the office.
• Each of these sections performs separate tasks.
• Two of these sections have multiple people that do the same job, but with different data results and turn in separate reports each week.
• These are then manually SUMed up to place in the report manually (What I am trying to get away from).

If there was a way that I could import these one at a time, and have the new data just add to what is already in the target cells, that would then provide me with a total for the "Team" for each day and task item.

Does that make more sense? I know it is sort of convoluted, but would be very helpful to all if possible.

Thanks Again!

 

[GlennUK]

Glenn,
First Answer...I understand how this works...not a problem. I was just thinking that if my master sheet (Target) was say...30 days long instead of 7, I could use the date as a vertical reference for multiple periods instead of weekly. Just more flexible. Anyway...not that important.

As to my second question on (SUM) issues...

• The Master Sheet (Target) has 5 separate sections, for the 5 sections in the office.
• Each of these sections performs separate tasks.
• Two of these sections have multiple people that do the same job, but with different data results and turn in separate reports each week.
• These are then manually SUMed up to place in the report manually (What I am trying to get away from).

If there was a way that I could import these one at a time, and have the new data just add to what is already in the target cells, that would then provide me with a total for the "Team" for each day and task item.

Does that make more sense? I know it is sort of convoluted, but would be very helpful to all if possible.

Thanks Again!

If you label the sections in every row of data then a PivotTable will give you what you want.

 

[chipsworld]

Glenn,
Thanks...Ill figure it out.

I appreciate everything you have helped me with to this point!
Stay strong, and stay safe!