VBA alternative to Index Match **Make it better**

[Roderick_E]

So I created a UDF that behaves like MATCH or INDEX MATCH but only on one criteria. I wish I knew enough to redesign it to function on unlimited criteria (within reason obviously). Here it is:

Function quickcompare(itemtofind As Range, comprange As Range, Optional resultreturn As Range)
'syntax quickcompare(cell,entire match column,optional entire result column)
‘syntax example: =quickcompare(A2,[Book2]Sheet1!$F:$F,[Book2]Sheet1!$G:$G) dragdown
On Error Resume Next
resultcol = ""
resultcol = Replace(Left(resultreturn.Address, InStr(resultreturn.Address, ":") - 1), "$", "")
hitrow = "Not found"
Resume Next
If IsError(Application.Match(itemtofind.Value, Workbooks(comprange.Parent.Parent.Name).Sheets(comprange.Parent.Name).Range(comprange.Address), 0)) = False Then
hitrow = Application.Match(itemtofind.Value, Workbooks(comprange.Parent.Parent.Name).Sheets(comprange.Parent.Name).Range(comprange.Address), 0)
End If
If resultcol = "" Then
quickcompare = hitrow
Else
If hitrow <> "Not Found" Then
quickcompare = Workbooks(comprange.Parent.Parent.Name).Sheets(comprange.Parent.Name).Cells(hitrow, resultcol)
Else
quickcompare = hitrow
End If
End If
End Function