Complex Index(Match) Problem

[Nlhicks]

This function works to get me the value I need for this instance however, when I change E3 and E4 to new values I get an error.
=INDEX('Facility Ratings & SOLs (Lines)'!$B$2:$B$685,MATCH(1,('Update Spreadsheet'!E3='Facility Ratings & SOLs (Lines)'!$J$2:$J$685)*('Update Spreadsheet'!E4='Facility Ratings & SOLs (Lines)'!$K$2:$K$685),0)

I am trying to turn this into VBA code that will do the same thing as the formula except allow for finding any of the values that match and returning the right value with this code:

Sub IndexMatchSubs()

Dim aSheet As Worksheet
Dim iSheet As Worksheet

Set aSheet = Worksheets("Update Spreadsheet")
Set iSheet = Worksheets("Facility Ratings & SOLs (Lines)")

aSheet.Range("E7").Value = Application.WorksheetFunction.Index(iSheet.Range("B2:B685"), Application.WorksheetFunction.Match(1, aSheet.Range("E3"), iSheet.Range("J2:J685"), 0) * Application.WorksheetFunction.Match(aSheet.Range("E4"), iSheet.Range("K2:K685"), 0))

End Sub

Any Ideas

 

[Legacy 143009]

What about if you split MATCH function?

=INDEX('Facility Ratings & SOLs (Lines)'!$B$2:$B$685,MATCH(1,'Update Spreadsheet'!E3='Facility Ratings & SOLs (Lines)'!$J$2:$J$685, 0)*MATCH(1, 'Update Spreadsheet'!E4='Facility Ratings & SOLs (Lines)'!$K$2:$K$685, 0),0)

 

[Nlhicks]

What about if you split MATCH function?

=INDEX('Facility Ratings & SOLs (Lines)'!$B$2:$B$685,MATCH(1,'Update Spreadsheet'!E3='Facility Ratings & SOLs (Lines)'!$J$2:$J$685, 0)*MATCH(1, 'Update Spreadsheet'!E4='Facility Ratings & SOLs (Lines)'!$K$2:$K$685, 0),0)

Thank you this is very helpful

 

[Legacy 143009]

Happy that worked.