How do I use Index & Match to reference a separate sheet?

[kminh]

I have the following test code that is working where column H is equal to what's in column B versus column D


Range("H2:H17") = "=INDEX(D2:D17,MATCH(B2:B17,B2:B17,0))"




Column B Column D Column H
A Test1 Test1
B Test2 Test2
C Test 3 Test3
D Test 4 Test4
E Test 5 Test5
F Test 6 Test6
G Test 7 Test7
H Test 8 Test8
I Test 9 Test9
J Test 10 Test10




Question:




How do I use the same code to reference a seperate table called "temp" to do the same thing. The idea is for each time the code looks for 'target' it does
an index and match checking column B to equal what's in column D so if the value A is passed then it would become Test1? I tried the following code, but get error "Sub or Function not defined"
when it comes to 'Match' and the code stops.




TargetPortF = Application.WorksheetFunction.Index(Sheets("Temp").Range("D2:D17"), Match(Sheets("Temp").Range("B2:B17"), Sheets("Temp").Range("B2:B17"), 0))




Any help is appreciated!

 

[Worf]

Try qualifying match:

b = WorksheetFunction.Index(Sheets("SI").[B2:B31], _
    WorksheetFunction.Match(a, Sheets("SI").[A2:A31], 0))