Offset in User Defined Function
 

I am trying to create a user defined function to simplify an INDEX-MATCH lookup

Public Function XLOOKUP(Range, Column, Row
XLOOKUP = Index(Range, Match(Row, Offset(Range, 0, 0, 0, 1), 0), Match(Column, Offset(Range, 0, 0, 1, 0), 0)
End Functio

However, when I use the function I get an error

Compile error
Sub or Function not define

and the first OFFSET function is highlighted

Any help would be appreciated

Thanks
Mark Graesse

Offset in User Defined Function
 

I figured out my first problem and rewrote the function

Public Function XLOOKUP(Range, Column, Row
XLOOKUP =
WorksheetFunction.Index(Range,
WorksheetFunction.Match(Row, WorksheetFunction.Offset(Range, 0, 0, , 1), 0),
WorksheetFunction.Match(Column, WorksheetFunction.Offset(Range, 0, 0, 1), 0)
End Functio

Now I get a #VALUE error when I use it. The formula works fine when I copy it into the worksheet, replaced the WorksheetFunction. with nothing and removed the XLOOKUP

Any ideas

Thanks
Mark Graesse


----- Mark Graesser wrote: ----

I am trying to create a user defined function to simplify an INDEX-MATCH lookup

Public Function XLOOKUP(Range, Column, Row
XLOOKUP = Index(Range, Match(Row, Offset(Range, 0, 0, 0, 1), 0), Match(Column, Offset(Range, 0, 0, 1, 0), 0)
End Functio

However, when I use the function I get an error

Compile error
Sub or Function not define

and the first OFFSET function is highlighted

Any help would be appreciated

Thanks
Mark Graesse