Index Match combination in VBA

Dear all,

I'm struggling with building a piece of VBA code which returns a productprice from a table.

The User-defined-function should have one variable/argument, which is the productnumber.

So in fact, in any cell in the entire worksheet can be searched for the price by using the user defined function: =Productprice("Apple")

Then, the function should search for the product in my range called "Producttable" and return the corresponding price for the product.

While this seems quite easy to achieve, i can't get it to work.

I'd like to use the Index and Match combination instead of the Vlookup function, for known reasons.

Attached i've got a sample file with the same names as above.

Also, i've got the tableheaders given a rangename which is: "ProductTableHeaders"


Who can help me in achieving my UDF?

Thanks in advance!
Best regards,
A.

Thanks for all the replies!

I've got it working with Keebellah's solution.

Thanks!
A.