Custom function that returns value from named table

[EnderSpy]

First of all, I'm quite of a begginer in VBA.

I'm trying to create a function that returns a cell inside a table if I specify the value of the row and column using a combination of INDEX and MATCH.

This is my code:

Function ReturnValueFromTable(Table As String, LookUpValue1 As String, LookUpArray1 As String, LookUpValue2 As String, LookUpArray2 As String) As Double

ReturnValueFromTable = Application.WorksheetFunction.Index(Range(Table), Application.WorksheetFunction.Match(LookUpValue1, Range(LookUpArray1), 0), Application.WorksheetFunction.Match(LookUpValue2, Range(LookUpArray2), 0))

End Function

Then when writing the function inside a cell I place named ranges in table, LookUpArray1 and LookUpArray2 and cells in LookUpValue1 and LookUpValue2

Something like this: =ReturnValueFromTable(Table1;M2;ExampleColumn;N2;ExampleRow)

It returns #VALUE!

 

[EnderSpy]

I used this as reference: How to perform a two-dimensional lookup

 

[mart37]

Function ReturnValueFromTable(Table As Range, LookUpValue1 As String, LookUpArray1 As Range, LookUpValue2 As String, LookUpArray2 As Range) As Double
    ReturnValueFromTable = Application.WorksheetFunction.Index(Table, Application.WorksheetFunction.Match(LookUpValue1, LookUpArray1, 0), Application.WorksheetFunction.Match(LookUpValue2, LookUpArray2, 0))
End Function

 

[EnderSpy]

Function ReturnValueFromTable(Table As Range, LookUpValue1 As String, LookUpArray1 As Range, LookUpValue2 As String, LookUpArray2 As Range) As Double
    ReturnValueFromTable = Application.WorksheetFunction.Index(Table, Application.WorksheetFunction.Match(LookUpValue1, LookUpArray1, 0), Application.WorksheetFunction.Match(LookUpValue2, LookUpArray2, 0))
End Function

I also tried that, and it still doesn't work

RimworldManager.xlsm
	P	Q	R	S	T	U	V	W
16		1	2	3	4	5		#VALUE!
17	1	11	12	13	14	15
18	2	21	22	23	24	24		1
19	3	31	32	33	34	35
20	4	41	42	43	44	45		2
21	5	51	52	53	54	55
RimworldManager Values

Cell Formulas
Range	Formula
W16	W16	=ReturnValueFromTable(table,W18,column,W20,row)

Named Ranges
Name	Refers To	Cells
column	='RimworldManager Values'!$P$17:$P$21	W16
row	='RimworldManager Values'!$Q$16:$U$16	W16
table	='RimworldManager Values'!$Q$17:$U$21	W16

 

[Fluff]

Try

Function ReturnValueFromTable(Table As Range, LookUpValue1 As Variant, LookUpArray1 As Range, LookUpValue2 As Variant, LookUpArray2 As Range) As Double
    ReturnValueFromTable = Application.Index(Table, Application.Match(LookUpValue1, LookUpArray1, 0), Application.Match(LookUpValue2, LookUpArray2, 0))
End Function

 

[EnderSpy]

Try

Function ReturnValueFromTable(Table As Range, LookUpValue1 As Variant, LookUpArray1 As Range, LookUpValue2 As Variant, LookUpArray2 As Range) As Double
    ReturnValueFromTable = Application.Index(Table, Application.Match(LookUpValue1, LookUpArray1, 0), Application.Match(LookUpValue2, LookUpArray2, 0))
End Function

It worked, thanks ?

 

[Fluff]

Glad we could help & thanks for the feedback.