VBA Index Match Function With Criteria Ranges In Another Worksheet

I am trying to do index match function with criteria ranges in another worksheets. The code kept on giving me " #Value!"..

Here is the code:

Sub test1()

Dim myvalue As Variant
Dim wsname As String
Dim i As Integer
Dim j As Integer
Dim r1 As range
Dim r2 As range
Dim r3 As range
Dim r4 As range

wsname = "CustomerIDbyParts"

[Code] .......

The resulting cell of F5 kept on returning #Value!.. I suspect that my syntax for looking up the ranges from another worksheet is wrong?