Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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' |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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' |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Exact Match using INDEX, MATCH | Excel Worksheet Functions | |||
index(match) Wind Uplift Calculations (match four conditions) | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
index,match,match on un-sorted data | Excel Worksheet Functions |