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