VBA code for Index Match function

[dss28]

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:

 

[offthelip]

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

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]

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

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]

I think I understand what you want try this:

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]

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 A	column B	column C	column D	column E	column F	column G	column H	column K
Row 1	headingA	headingB	headingC	headingD	headingE	headingF	headingG	headingH	headingK
2						fd001		1
3						fd001		2
4						fd001		3
5						fd002		1
6						fd002		2
7						fd003		1
8						fd003		2
9						fd003		3

in Sheet "Report"

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

column C

column D

columnE

column F

column G

column H

Row 13	Header H	Header C	Header F	Header G	Header K
14	1
15	2
16	3
17	4
18	5
19	6
20	7
21	8
22	9

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]

try this, your example of the data you have helps alot.

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]

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

 

[offthelip]

On the report sheet , which column are the numbers 1 to 14, it works in my test

 

[dss28]

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]

Can you post a screen shot of the MasterData please, or load with xlbb