Hi,
I've tried to find a post similar to my problem, but I haven't had
much luck and my knowledge of the subject is extremely lacking. I'm
trying to find a value based upon a double match. Sheet 1 has a named
table (w/headers) "EmpData" and sheet 3 has the lookup criteria
(employee number (K4) and supervisor's name (I2)) and will contain the
result of the search. The search will look in EmpData to find the
supervisor's name (col. "P") and the employee's number (col"E") . When
a match is found, I need the value from column "O" returned to sheet
3. I have tried the following code and some variations of it, but to
no avail. Does anyone know what I'm doing wrong?

LastRow = Range("P" & Rows.Count).End(xlUp).Row
For r = LastRow To 4 Step -1
Cells(r, "P").Offset(0, 5).Value = "=INDEX(EmpData,MATCH(I2,P3:P" & r
& ",0),MATCH(K" & r & ",E3:E" & r & ",0))"
Next r

Thanks.
James

hi James,

x = Evaluate("=INDEX(EmpData,MATCH(I2,P3:P" & r & ",0),MATCH(K" & r & ",E3:E" & r & ",0))")
Cells(r, "P").Offset(0, 5).Value = x


--
isabelle



Le 2012-01-21 14:55, John Smith a écrit :
Hi,
I've tried to find a post similar to my problem, but I haven't had
much luck and my knowledge of the subject is extremely lacking. I'm
trying to find a value based upon a double match. Sheet 1 has a named
table (w/headers) "EmpData" and sheet 3 has the lookup criteria
(employee number (K4) and supervisor's name (I2)) and will contain the
result of the search. The search will look in EmpData to find the
supervisor's name (col. "P") and the employee's number (col"E") . When
a match is found, I need the value from column "O" returned to sheet
3. I have tried the following code and some variations of it, but to
no avail. Does anyone know what I'm doing wrong?

LastRow = Range("P"& Rows.Count).End(xlUp).Row
For r = LastRow To 4 Step -1
Cells(r, "P").Offset(0, 5).Value = "=INDEX(EmpData,MATCH(I2,P3:P"& r
& ",0),MATCH(K"& r& ",E3:E"& r& ",0))"
Next r

Thanks.
James

On Jan 21, 5:19*pm, isabelle wrote:
hi James,

x = Evaluate("=INDEX(EmpData,MATCH(I2,P3:P" & r & ",0),MATCH(K" & r & ",E3:E" & r & ",0))")
Cells(r, "P").Offset(0, 5).Value = x

--
isabelle

Le 2012-01-21 14:55, John Smith a crit :



Hi,
I've tried to find a post similar to my problem, but I haven't had
much luck and my knowledge of the subject is extremely lacking. I'm
trying to find a value based upon a double match. Sheet 1 has a named
table (w/headers) "EmpData" and sheet 3 has the lookup criteria
(employee number (K4) and supervisor's name (I2)) and will contain the
result of the search. The search will look in EmpData to find the
supervisor's name (col. "P") and the employee's number (col"E") . When
a match is found, I need the value from column "O" returned to sheet
3. I have tried the following code and some variations of it, but to
no avail. Does anyone know what I'm doing wrong?

LastRow = Range("P"& *Rows.Count).End(xlUp).Row
For r = LastRow To 4 Step -1
Cells(r, "P").Offset(0, 5).Value = "=INDEX(EmpData,MATCH(I2,P3:P"& *r
& *",0),MATCH(K"& *r& *",E3:E"& *r& *",0))"
Next r

Thanks.
James- Hide quoted text -

- Show quoted text -

Thank you, Isabelle. It gets me a lot closer, but now I'm getting a
2042 error on x.

ok, the INDEX function can have only one row number argument

x = Evaluate("=INDEX(EmpData,MATCH(K" & r & ",E3:E" & r & ",0))")
y = Evaluate("=INDEX(EmpData,MATCH(I2,P3:P" & r & ",0))")
Cells(r, "P").Offset(0, 5).Value = x & " " & y


--
isabelle




Le 2012-01-21 18:27, John Smith a écrit :

Thank you, Isabelle. It gets me a lot closer, but now I'm getting a
2042 error on x.

On Jan 21, 5:41*pm, isabelle wrote:
ok, the INDEX function can have only one row number argument

x = Evaluate("=INDEX(EmpData,MATCH(K" & r & ",E3:E" & r & ",0))")
y = Evaluate("=INDEX(EmpData,MATCH(I2,P3:P" & r & ",0))")
Cells(r, "P").Offset(0, 5).Value = x & " " & y

--
isabelle

Le 2012-01-21 18:27, John Smith a crit :



Thank you, Isabelle. It gets me a lot closer, but now I'm getting a
2042 error on x.- Hide quoted text -

- Show quoted text -

Ok, this is what I get now:
x = Evaluate("=INDEX(EmpData,MATCH(K" & r & ",E3:E" & r & ",0))") ==
Error 2042
y = Evaluate("=INDEX(EmpData,MATCH(I2,P3:P" & r & ",0))") == Error
2042
Cells(r, "P").Offset(0, 5).Value = x & " " & y == Run time error 13
"Type mismatch'

it is imperative that the values looked for to be present in the search range

exemple : http://cjoint.com/?BAwcieRexMt

--
isabelle



Le 2012-01-21 18:48, John Smith a écrit :


Ok, this is what I get now:
x = Evaluate("=INDEX(EmpData,MATCH(K"& r& ",E3:E"& r& ",0))") ==
Error 2042
y = Evaluate("=INDEX(EmpData,MATCH(I2,P3:P"& r& ",0))") == Error
2042
Cells(r, "P").Offset(0, 5).Value = x& " "& y == Run time error 13
"Type mismatch'