Index and Match
 

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

Index and Match
 

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

Index and Match
 

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.

Index and Match
 

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.

Index and Match
 

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'

Index and Match
 

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'

Index and Match
 

On Jan 21, 7:09*pm, isabelle wrote:
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'- Hide quoted text -

- Show quoted text -

The values are there, but let me give you an example of what kind of
works. If I use the following:

..Offset(0, 5).Value = "=VLOOKUP(K" & r & " , EmpData, 11, False)"

it will give me the right answer only by chance. The problem is that
it returns the first value that it finds for the employee which may,
or may not, match up with the supervisor's name. The range may look
like this:

Supervisor
(E)
Work Area(O) Employee(P)
Jones
1234 Smith
Bennett
2398 Smith
Thomas
5555 Smith

If I select employee Smith (sh.3,K) and supervisor Thomas (sh.3,I),
then I need 5555 returned to sheet 3. The vlookup will always return
1234. Does that make it any clearer? Maybe there is a different way
to get the right answer?
James

Index and Match
 

i do not see how the data are placed, can you put a file online

--
isabelle



Le 2012-01-21 20:42, John Smith a écrit :


The values are there, but let me give you an example of what kind of
works. If I use the following:

.Offset(0, 5).Value = "=VLOOKUP(K"& r& " , EmpData, 11, False)"

it will give me the right answer only by chance. The problem is that
it returns the first value that it finds for the employee which may,
or may not, match up with the supervisor's name. The range may look
like this:

Supervisor
(E)
Work Area(O) Employee(P)
Jones
1234 Smith
Bennett
2398 Smith
Thomas
5555 Smith

If I select employee Smith (sh.3,K) and supervisor Thomas (sh.3,I),
then I need 5555 returned to sheet 3. The vlookup will always return
1234. Does that make it any clearer? Maybe there is a different way
to get the right answer?
James

Index and Match
 

On Jan 21, 8:44*pm, isabelle wrote:
i do not see how the data are placed, can you put a file online

--
isabelle

Le 2012-01-21 20:42, John Smith a écrit :





The values are there, but let me give you an example of what kind of
works. If I use the following:

.Offset(0, 5).Value = "=VLOOKUP(K"& *r& *" , EmpData, 11, False)"

it will give me the right answer only by chance. The problem is that
it returns the first value that it finds for the employee which may,
or may not, match up with the supervisor's name. The range may look
like this:

Supervisor
(E)
Work Area(O) * Employee(P)
Jones
1234 * * * * * * Smith
Bennett
2398 * * * * * *Smith
Thomas
5555 * * * * * * Smith

If I select employee Smith (sh.3,K) and supervisor Thomas (sh.3,I),
then I need 5555 returned to sheet 3. The vlookup will always return
1234. *Does that make it any clearer? Maybe there is a different way
to get the right answer?
James- Hide quoted text -

- Show quoted text -

I can send a stripped down version directly to you, but I can't get
permission to post it. Will that work?