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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

dss28

Board Regular
Joined
Sep 3, 2020
Messages
61
Office Version
  1. 2007
Platform
  1. Windows
index match function vba code.xlsm
ABCDEFGHIJKLMNO
1Column A HeadingColumn B HeadingColumn C HeadingColumn D HeadingColumn E HeadingColumn F HeadingColumn G HeadingColumn H HeadingColumn I HeadingColumn J HeadingColumn K HeadingColumn L HeadingColumn M HeadingColumn N HeadingColumn O Heading
2fd0011
3fd0012
4fd0013
5fd0014
6fd0021
7fd0022
8fd0023
9fd0024
10fd0025
11fd0031
12fd0032
13fd0033
14fd0034
15fd0035
16fd0036
17fd0037
18fd0038
19fd0041
20fd0042
21fd0043
22fd0044
23fd0045
24fd0046
25fd0047
26fd0048
27fd0049
28fd00410
29fd00411
30fd00412
31fd00413
MasterData
 

dss28

Board Regular
Joined
Sep 3, 2020
Messages
61
Office Version
  1. 2007
Platform
  1. Windows
sheet "MasterData" in the previous post and Sheet Report below

index match function vba code.xlsm
ABCDEFGHIJKLM
1
2
3
4
5
6
7Colmn F Heading
8
9
10
11
12
13Colmn H HeadingColmn C HeadingColmn F HeadingColmn G HeadingColmn K Heading
141
152
163
174
185
196
207
218
229
2310
2411
2512
2613
2714
2815
29
30
31
Report
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,732
Office Version
  1. 2010
Platform
  1. Windows
That looks identical to my workheets. Where have you put the code? You should put in a standard module NOT in the worksheet code.
 
Solution

dss28

Board Regular
Joined
Sep 3, 2020
Messages
61
Office Version
  1. 2007
Platform
  1. Windows
That looks identical to my workheets. Where have you put the code? You should put in a standard module NOT in the worksheet code.
yes, great , that worked.. ..realised the mistake
thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,127,529
Messages
5,625,352
Members
416,096
Latest member
forevans

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