VBA code for Index Match function

dss28

Board Regular
Joined
Sep 3, 2020
Messages
61
Office Version
  1. 2007
Platform
  1. Windows
I am using Index match function in excel to retrieve values from a sheet ("MasterData") to another sheet ("Report") using a specific unique number which the user enters in a cell in sheet "Report" . This populates a table with about 6 columns and 15 rows as per the data available in "MasterSheet" for that particular unique number.

for this I have entered this formula in each of the cells in sheet "Report" in the 15x6 rows/ column table.

formula is :

{=INDEX(MasterData!C:C,MATCH($E$7&"|"&$C$14,MasterData!F:F&"|"&MasterData!H:H,0))}

In this formula the cell reference $E$7 and $C$14 is in sheet "Report".

The user enters the specific unique code in cell C14 which then matches the values of each row reference cell E7 to pull the corresponding values in the table on this sheet.

Now I want to convert this formula to vba code.

can any body help me to convert this in vba code:
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,729
Office Version
  1. 2010
Platform
  1. Windows
Here is some VBa that will do the double index match you requested, however you haven't specified where you want the output nor exactly which 6 colums you want copied across.
The code is very easy to modify just by changing some of the numbers. It will write the output into columns T to Y on the reports sheet and is currently copying columns D to I
VBA Code:
Sub test()
' load all the dat from master data into a variant array
With Worksheets("Masterdata")
lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
lastrow = .Cells(Rows.Count, "C").End(xlUp).Row
inarr = .Range(.Cells(1, 1), .Cells(lastrow, lastcol))
End With
With Worksheets("Report")
e7 = .Range(.Cells(7, 5), .Cells(7, 5))
c14 = .Range(.Cells(14, 3), .Cells(14, 3))
' clear output area ( you haven't said where you want the output)
.Range(.Cells(1, 20), .Cells(lastrow, 26)) = ""
outarr = .Range(.Cells(1, 20), .Cells(lastrow, 26))
indi = 1
For i = 1 To lastrow
  If e7 = inarr(i, 6) And c14 = inarr(i, 8) Then
   ' copy the row to output ( iam not sure which columns you want copying!!
    For j = 1 To 6
     outarr(indi, j) = inarr(i, 3 + j) ' change the 3 to change the start columm or chang to individual column copies
    Next j
    indi = indi + 1
   End If
Next i

' write the output
.Range(.Cells(1, 20), .Cells(lastrow, 26)) = outarr
End With
End Sub
 

dss28

Board Regular
Joined
Sep 3, 2020
Messages
61
Office Version
  1. 2007
Platform
  1. Windows
Here is some VBa that will do the double index match you requested, however you haven't specified where you want the output nor exactly which 6 colums you want copied across.
The code is very easy to modify just by changing some of the numbers. It will write the output into columns T to Y on the reports sheet and is currently copying columns D to I
VBA Code:
Sub test()
' load all the dat from master data into a variant array
With Worksheets("Masterdata")
lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
lastrow = .Cells(Rows.Count, "C").End(xlUp).Row
inarr = .Range(.Cells(1, 1), .Cells(lastrow, lastcol))
End With
With Worksheets("Report")
e7 = .Range(.Cells(7, 5), .Cells(7, 5))
c14 = .Range(.Cells(14, 3), .Cells(14, 3))
' clear output area ( you haven't said where you want the output)
.Range(.Cells(1, 20), .Cells(lastrow, 26)) = ""
outarr = .Range(.Cells(1, 20), .Cells(lastrow, 26))
indi = 1
For i = 1 To lastrow
  If e7 = inarr(i, 6) And c14 = inarr(i, 8) Then
   ' copy the row to output ( iam not sure which columns you want copying!!
    For j = 1 To 6
     outarr(indi, j) = inarr(i, 3 + j) ' change the 3 to change the start columm or chang to individual column copies
    Next j
    indi = indi + 1
   End If
Next i

' write the output
.Range(.Cells(1, 20), .Cells(lastrow, 26)) = outarr
End With
End Sub
Thanks you very much for responding.

to give more clarity on this : -

the sheet "MasterData" has a data in more than 30 columns. From this data I am extracting some column values which may not be adjacent to each other.

In this particular case, in the sheet "Report", the user enter a value in cell E7. The table in sheet "Report" is in range D14:G28 for populating / extracting the values from sheet "MasterData".

Each cell in this rangeD14:G28 in sheet "Report" is having the formula -

eg. first row
Cell D14 has formula - {=INDEX(MasterData!C:C,MATCH($E$7&"|"&$C$14,MasterData!F:F&"|"&MasterData!H:H,0))}
Cell E14 has formula - {=INDEX(MasterData!F:F,MATCH($E$7&"|"&$C$14,MasterData!F:F&"|"&MasterData!H:H,0))}
Cell F14 has formula - {=INDEX(MasterData!G:G,MATCH($E$7&"|"&$C$14,MasterData!F:F&"|"&MasterData!H:H,0))}
Cell G14 has formula - {=INDEX(MasterData!K:K,MATCH($E$7&"|"&$C$14,MasterData!F:F&"|"&MasterData!H:H,0))}

2nd row

Cell D15 has formula - {=INDEX(MasterData!C:C,MATCH($E$7&"|"&$C$15,MasterData!F:F&"|"&MasterData!H:H,0))}
Cell E15 has formula - {=INDEX(MasterData!F:F,MATCH($E$7&"|"&$C$15,MasterData!F:F&"|"&MasterData!H:H,0))}
Cell F15 has formula - {=INDEX(MasterData!G:G,MATCH($E$7&"|"&$C$15,MasterData!F:F&"|"&MasterData!H:H,0))}
Cell G15 has formula - {=INDEX(MasterData!K:K,MATCH($E$7&"|"&$C$15,MasterData!F:F&"|"&MasterData!H:H,0))}

etc. to the last row D28
28th row
Cell D28 has formula - {=INDEX(MasterData!C:C,MATCH($E$7&"|"&$C$28,MasterData!F:F&"|"&MasterData!H:H,0))}
Cell E28 has formula - {=INDEX(MasterData!F:F,MATCH($E$7&"|"&$C$28,MasterData!F:F&"|"&MasterData!H:H,0))}
Cell F28 has formula - {=INDEX(MasterData!G:G,MATCH($E$7&"|"&$C$28,MasterData!F:F&"|"&MasterData!H:H,0))}
Cell G28 has formula - {=INDEX(MasterData!K:K,MATCH($E$7&"|"&$C$28,MasterData!F:F&"|"&MasterData!H:H,0))}
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,729
Office Version
  1. 2010
Platform
  1. Windows
I think I understand what you want try this:
VBA Code:
Sub test()
' load all the data from master data into a variant array
With Worksheets("Masterdata")
lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
lastrow = .Cells(Rows.Count, "C").End(xlUp).Row
inarr = .Range(.Cells(1, 1), .Cells(lastrow, lastcol))
End With
With Worksheets("Report")

e7 = .Range(.Cells(7, 5), .Cells(7, 5))
colcdata = .Range(.Cells(14, 3), .Cells(28, 3))
' clear output area D14:G28
.Range(.Cells(14, 4), .Cells(28, 7)) = ""
outarr = Range(.Cells(14, 4), .Cells(28, 7))
indi = 1
For i = 1 To 14  ' rows 14 to 28
    For j = 1 To lastrow ' loop throgh master data
  
    If e7 = inarr(j, 6) And colcdata(i, 1) = inarr(j, 8) Then
   ' copy the row to output ( iam not sure which columns you want copying!!
        outarr(i, 1) = inarr(j, 3)  ' Col C to Col D
        outarr(i, 2) = inarr(j, 6)  ' Col F to Col E
        outarr(i, 3) = inarr(j, 7)  ' Col G to Col F
        outarr(i, 4) = inarr(j, 11)  ' Col K to Col G
     End If
    Next j
Next i

' write the output
.Range(.Cells(14, 4), .Cells(28, 7)) = outarr
End With
End Sub
 

dss28

Board Regular
Joined
Sep 3, 2020
Messages
61
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

thanks sir for reply i tried this but no data was transferred to sheet "Report".

I am trying to include the two tables in these sheets here as follows :


Sheet "MasterData"

column Acolumn Bcolumn Ccolumn Dcolumn Ecolumn Fcolumn Gcolumn Hcolumn K
Row 1headingAheadingBheadingCheadingDheadingEheadingFheadingGheadingHheadingK
2
fd001
1
3
fd001
2
4fd0013
5fd0021
6fd0022
7fd0031
8fd0032
9fd0033


in Sheet "Report"

in Cell E7 - user will enter a value corresponding to values in heading F column in sheet "MasterData"

column Ccolumn DcolumnEcolumn Fcolumn Gcolumn H
Row 13Header HHeader CHeader FHeader GHeader K
141
152
163
174
185
196
207
218
229
and so on to row 28


In the above table in sheet "Report", In column C/ row14 - the values under header F and H of sheet Masterdata corresponding to E7 cell value are matched and corresponding column values of under header C, F, G, K of sheet "MasterData" are populated

if user enters value fd001 (Column F value) in cell E7, it will populate data of 3 rows - 2,3,4 from masterdata sheet corresponding to column C, F, G, K columns in row 14,15,16 corresponding to 1,2,3 values in column C of sheet "Report" which has values similar to header H in sheet"MasterData"

similarly
if user enters value fd002 in cell E7 it will populate data of 2 rows - 5,6 from masterdata sheet
if user enters value fd003 in cell E7 it will populate data of 3 rows - 7,8,9 from masterdata sheet


i hope i am able to explain properly
request your help again..
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,729
Office Version
  1. 2010
Platform
  1. Windows
try this, your example of the data you have helps alot.
VBA Code:
Sub test()
' load all the data from master data into a variant array
With Worksheets("Masterdata")
lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
lastrow = .Cells(Rows.Count, "C").End(xlUp).Row
inarr = .Range(.Cells(1, 1), .Cells(lastrow, lastcol))
End With
With Worksheets("Report")

e7 = .Range(.Cells(7, 5), .Cells(7, 5))
colcdata = .Range(.Cells(14, 3), .Cells(28, 3))
' clear output area D14:G28
.Range(.Cells(14, 4), .Cells(28, 7)) = ""
outarr = Range(.Cells(14, 4), .Cells(28, 7))
indi = 1
For i = 1 To lastrow ' loop throgh master data
   If e7 = inarr(i, 6) Then ' found e7 valuer in master data,
     For j = 1 To 14 ' now loop through report row s to find match to Column H
      If colcdata(j, 1) = inarr(i, 8) Then
   ' copy the row to output ( iam not sure which columns you want copying!!
        outarr(j, 1) = inarr(i, 3)  ' Col C to Col D
        outarr(j, 2) = inarr(i, 6)  ' Col F to Col E
        outarr(j, 3) = inarr(i, 7)  ' Col G to Col F
        outarr(j, 4) = inarr(i, 11)  ' Col K to Col G
     End If
    Next j
  End If
Next i

' write the output
.Range(.Cells(14, 4), .Cells(28, 7)) = outarr
End With
End Sub
 

dss28

Board Regular
Joined
Sep 3, 2020
Messages
61
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

I corrected the sheet name to "MasterData" instead of "Masterdata" but no data transfer happening
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,729
Office Version
  1. 2010
Platform
  1. Windows
On the report sheet , which column are the numbers 1 to 14, it works in my test
 

Attachments

  • Capture.JPG
    Capture.JPG
    61.6 KB · Views: 3
Last edited:

dss28

Board Regular
Joined
Sep 3, 2020
Messages
61
Office Version
  1. 2007
Platform
  1. Windows
On the report sheet , which column are the numbers 1 to 14
in sheet "Report" they are on column C starting from row 14 to row 28 ------ in all 15 numbers
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,729
Office Version
  1. 2010
Platform
  1. Windows
Can you post a screen shot of the MasterData please, or load with xlbb
 

Watch MrExcel Video

Forum statistics

Threads
1,127,318
Messages
5,623,974
Members
416,002
Latest member
Neshx

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
Top