WorkSheet Function

[Dthmtlgod]

I am trying to return a value (RES) using INDEX/MATCH in VBA.

Res = WorksheetFunction.Index (Range("LOOKUP81!$A$1:$B$3",MATCH(Range("81!F"
& Z),Range("LOOKUP81!$A$1:$A$3),0,2)))

I trying to find a match and return a value. The statement continues to
error out. It works fine as a formula through the application =INDEX
(LOOKUP81!$A$1:$B$3,MATCH($F5,LOOKUP81!$A$1:$A$3,0),2)

Please assist.

Thanks

 

[Frank Kabel]

Hi
there are some missing parenthesis. Try
Res = WorksheetFunction.Index
(Range("LOOKUP81!$A$1:$B$3"),MATCH(Range("81!F"&
Z),Range("LOOKUP81!$A$1:$A$3"),0),2)

 

[Dthmtlgod]

Thank Frank.

I changed it to the following

Res = WorksheetFunction.Index(Range("LOOKUP81!$A$1:$B$3"),
WorksheetFunction.Match(Range("81!F" & Z), Range("LOOKUP81!$A$1:$A$3"), 0),
2)

I added the WorksheetFunction.Match, now I am receiving a

Run-time error '1004'
Unable to get the Match property of the WorksheetFunction class

????

Am I missing something in the Match function?

 

[Frank Kabel]

Hi
try
with application.worksheetfunction
Res = .Index(Range("LOOKUP81!$A$1:$B$3"),.Match(Range("LOOKUP81!F"
& Z), _
Range("LOOKUP81!$A$1:$A$3"), 0),2)
end with

 

[Dthmtlgod]

Hi Frank.

I am still receiving the same error. I added the With
Application.WorksheetFunction, etc.

Run-time error '1004'
Unable to get the Match property of the WorksheetFunction class

Any ideas?

 

[Frank Kabel]

Hi
are you sure the range names are correct 8e.g. the sheet names exist,
etc.)