INDEX and MATCH with a function in VBA

[Behelith]

I'm trying to create a function in VBA to simplify a INDEX and MATCH formula that I use very often.
The INDEX and MATCH formula looks like this: =INDEX(Range1, MATCH(CellToMatch, Range2, 0), 1)
The CellToMatch is allways on the active sheet, Range1 and Range2 are usually on another sheet.
Since this is a function I don't know where the CellToMatch and ranges are.
The selections can be from a range or from a table.

The vba code that I have now is:

Public Function IndexMatch(CellToMatch As Range, Arr1 As Range, Arr2 As Range)

IndexMatch = Application.WorksheetFunction.Index(Range(Arr1), Application.WorksheetFunction.Match(Range(CellToMatch).Value, Range(Arr2), 0), 1)

End Function

The the function results in a #VALUE !

 

[Fluff]

Try

Public Function IndexMatch(CellToMatch As Range, Arr1 As Range, Arr2 As Range)

IndexMatch = Application.Index(Arr1, Application.Match(CellToMatch.Value, Arr2, 0), 1)
End Function

 

[Behelith]

Thanks Fluff, it works!

 

[Behelith]

I added IFNA to the function to set the result to "" if the indexmatch function returns an #N/A.
You can replace the "" with Blank if you need a 0 as output for calculations.

Public Function IndexMatch(CellToMatch As Range, Arr1 As Range, Arr2 As Range)

IndexMatch = Application.IfNa(Application.Index(Arr1, Application.Match(CellToMatch.Value, Arr2, 0), 1), "")

End Function

 

[Fluff]

Rather than using IFNA, you can check if an error is produced like

Public Function IndexMatch(CellToMatch As Range, Arr1 As Range, Arr2 As Range)

IndexMatch = Application.Index(Arr1, Application.Match(CellToMatch.Value, Arr2, 0), 1)
If IsError(IndexMatch) Then IndexMatch = ""
End Function