0

I am trying to build lookup function in VBA imitating formula:

=INDEX(get_column, MATCH(lookup, lookup_column,0),1)

I have cooked this:

Public Function IndexMatch(get_column As Range, lookup As Range, lookup_column As Range) As Variant
    IndexMatch = Application.WorksheetFunction.Index(get_column, Application.WorksheetFunction.Match(lookup, lookup_column, 0), 1)
End Function

I keep getting a #VALUE! error.

UPDATE (following comments of KS Sheon). So this function actually works, the problem was in having VBA function in a different workbook.

Przemyslaw Remin
  • 6,276
  • 25
  • 113
  • 191
  • 1
    does your lookup value exist ? i tried your code it works actually. the error #VALUE means the lookup value isn't found. – Rosetta Jan 15 '16 at 10:44
  • btw you can use `worksheetfunction.index` instead of `application.worksheetfunction.index`... dropping `application.` – Rosetta Jan 15 '16 at 10:45
  • Yes, it works. The problem was caused by having the code in a different workbook. It is strange since it is a `Public Function` and I thought it did not matter it is in another workbook. Is there any benefit with dropping `application`? – Przemyslaw Remin Jan 15 '16 at 10:56
  • Save the function as Excel Add-in file (.xlam) and load the add-in. Then you can use the function over any workbook. drop application. to beautify, not sure if its a benefit :) – Rosetta Jan 15 '16 at 11:10

0 Answers0