Index and Match Function in a Macro or VBA

pawcoyote

Board Regular
Joined
Mar 5, 2012
Messages
50
Howdy all,

I would like to convert the following Index Match and IF statements into a Macro or VBA script.

My workbooks are so bagged down by all the Matches and statements I am running and need to figure out how to clean it up.

I have One workbook with multiple worksheets that I look up and match info on and pull into the various sheets.

Below is the Index match that I use... It is very simple. one thing I would love to be able to do as well is remove all the zeros and #NA that show if nothing matches. I have roughly 25 of these on one worksheet looking for data on the MAFFT and pulling back the info into the Deployment Sheet.

=INDEX(MAFFT!$A$4:$CZ$5000,MATCH($V68,MAFFT!$CC$4:$CC5058,0),98)

Below is the IF Statement that I use.. Again simple...

=IF('MAFFT'!F7=0,"",'MAFFT'!H7)

Thank you for any help.
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

BarryL

Well-known Member
Joined
Jan 20, 2014
Messages
1,421
had no idea where this formula is input so assumed in column BA. try the below

Code:
Sub EvalIndexMatch()
Dim addr As String
addr = "BA4:BA5000"
Range(addr).Value = Evaluate("IFERROR(INDEX(MAFFT!$A$4:$CZ$5000,MATCH($V68,MAFFT!$CC$4:$CC5058,0),98),"""")")
End Sub
 

pawcoyote

Board Regular
Joined
Mar 5, 2012
Messages
50
Sorry The Fields that the formula will input to will be different, I have 25 different fields that I will be putting info into..

i.e. Sheet Deployment field C4 will have info from field 98 on the MAFFT Sheet, Deployment field C10 will have info from field 17 on the MAFFT sheet etc...

I am not sure what you mean by use code tags...

Thanks,
 

BarryL

Well-known Member
Joined
Jan 20, 2014
Messages
1,421
you stated "I would like to convert the following Index Match and IF statements into a Macro or <acronym title="visual basic for applications">VBA</acronym> script." not sure where these other fields came from, either way you need to post sample data and expected output.
 

pawcoyote

Board Regular
Joined
Mar 5, 2012
Messages
50

ADVERTISEMENT

How can I upload a sample excel file with the fields and info?

What I want to be able to do is this.

Deployment Spreadsheet, Cell D2 Type in a Number i.e. (C12345678), this is the number we will be matching on in the other Sheets and pulling in info from those sheets based off of this match.

Deployment Sheet, cell F2 will fill in with the information based off of the below Index Match. I want to make this as simple as possible. I have 25 cells that will need information copied over based off the match of D2 on the Deployment sheet.

=INDEX(MAFFT!$A$4:$CZ$5058,MATCH($D2,MAFFT!$G$2:$G5058,0),2)
 

pawcoyote

Board Regular
Joined
Mar 5, 2012
Messages
50
I hope this is more clear on what I want to do. I am very very new to scripting and VBA.

In A2 on the Deployment sheet you will type in the ConFigSN. It will then look up on the MAFFT sheet to see if there is a match and if so it will fill in the related fields B2:F2 with their specific data. I am also trying not to get Zero's and #NA in the fields.

=INDEX(MAFFT!$A$2:$CZ$5058,MATCH($B2,MAFFT!$A$2:$A5058,0),2) 2 is cell position where the MFG is located.

Deployment Sheet
A2 = ConfigSN (Look up Criteria (i.e. C12345678)
B2 = MFG
C2 = Model Name
D2 = Model
E2 = Serial Number
F2 = Equipment ID

Below is the sheet that will have the information we need to pull into the Deployment sheet.

MAFFT Sheet
A2 = ConfigSN
B2 = MFG
C2 = Model Name
D2 = Model
E2 = Serial Number
F2 = Equipment ID

Thank you for any help you can provide.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,576
Messages
5,596,987
Members
414,115
Latest member
SFUser

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