Index and Match

  • Thread starter Thread starter John Smith
  • Start date Start date
J

John Smith

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 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 :






- Show quoted text -
Click to expand...

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 :
 
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 :




- Show quoted text -
Click to expand...

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 :






- Show quoted text -
Click to expand...

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
 
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 :
 
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 :










- Show quoted text -
Click to expand...

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