Two Dimensional Lookup with Match and Index for multiple columns

chipsworld

Board Regular
Joined
May 23, 2019
Messages
169
Office Version
  1. 365
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...

VBA Code:
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
 
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!
 
Upvote 0

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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.
 
Upvote 0
Glenn,
Thanks...Ill figure it out.

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

Forum statistics

Threads
1,224,885
Messages
6,181,588
Members
453,055
Latest member
cope7895

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