Index and Match Function in a Macro or VBA

[pawcoyote]

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.

 

[BarryL]

had no idea where this formula is input so assumed in column BA. try the below

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]

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]

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]

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]

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.

 

[pawcoyote]

Hi, any help in what I would like to do?