Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Returning a text string after a 2 criteria search
Hello,
I have a list of workers that carried out multiple actions on a given day. What I wish to do is a apply the position they were assigned to on a given day to every action. I have a table of actions by date and worker and a table of positions by date and worker. For example: Table 1 Actions Brian 22/4 cleaning David 22/4 washing Brian 22/4 gardening David 22/4 running Sally 23/4 cooking Table 2 Position Brian 22/4 Supervisor David 22/4 Assistant Sally 23/4 Trainee In table 1 I wish to assign to column D the position. Therefore for the entries for David should have assistant and Brian should have Supervisor in the 4th column table 1. Any help would be greatly appreciated. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Returning a text string after a 2 criteria search
Assuming Table 1 is in A1:C20 and table 2 is in M1:O20
=INDEX($O$1:$O$20,MATCH(1,($M$1:$M$20=A1)*($N$1:$N $20=B1),0)) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Spag" wrote in message ... Hello, I have a list of workers that carried out multiple actions on a given day. What I wish to do is a apply the position they were assigned to on a given day to every action. I have a table of actions by date and worker and a table of positions by date and worker. For example: Table 1 Actions Brian 22/4 cleaning David 22/4 washing Brian 22/4 gardening David 22/4 running Sally 23/4 cooking Table 2 Position Brian 22/4 Supervisor David 22/4 Assistant Sally 23/4 Trainee In table 1 I wish to assign to column D the position. Therefore for the entries for David should have assistant and Brian should have Supervisor in the 4th column table 1. Any help would be greatly appreciated. Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Returning a text string after a 2 criteria search
Thanks Bob,
I've put the formula in but am getting #N/A in return. I've made sure the dates are formatted correctly and have cross referenced to make sure that the MATCH"date" and MATCH"worker" are column and cell correct. Is there anything else I should be checking? "Bob Phillips" wrote: Assuming Table 1 is in A1:C20 and table 2 is in M1:O20 =INDEX($O$1:$O$20,MATCH(1,($M$1:$M$20=A1)*($N$1:$N $20=B1),0)) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Spag" wrote in message ... Hello, I have a list of workers that carried out multiple actions on a given day. What I wish to do is a apply the position they were assigned to on a given day to every action. I have a table of actions by date and worker and a table of positions by date and worker. For example: Table 1 Actions Brian 22/4 cleaning David 22/4 washing Brian 22/4 gardening David 22/4 running Sally 23/4 cooking Table 2 Position Brian 22/4 Supervisor David 22/4 Assistant Sally 23/4 Trainee In table 1 I wish to assign to column D the position. Therefore for the entries for David should have assistant and Brian should have Supervisor in the 4th column table 1. Any help would be greatly appreciated. Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Returning a text string after a 2 criteria search
I forgot to mention that it is an array formula, so you need to
Ctrl-Shift-Enter after entering the formula, not just Enter. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Spag" wrote in message ... Thanks Bob, I've put the formula in but am getting #N/A in return. I've made sure the dates are formatted correctly and have cross referenced to make sure that the MATCH"date" and MATCH"worker" are column and cell correct. Is there anything else I should be checking? "Bob Phillips" wrote: Assuming Table 1 is in A1:C20 and table 2 is in M1:O20 =INDEX($O$1:$O$20,MATCH(1,($M$1:$M$20=A1)*($N$1:$N $20=B1),0)) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Spag" wrote in message ... Hello, I have a list of workers that carried out multiple actions on a given day. What I wish to do is a apply the position they were assigned to on a given day to every action. I have a table of actions by date and worker and a table of positions by date and worker. For example: Table 1 Actions Brian 22/4 cleaning David 22/4 washing Brian 22/4 gardening David 22/4 running Sally 23/4 cooking Table 2 Position Brian 22/4 Supervisor David 22/4 Assistant Sally 23/4 Trainee In table 1 I wish to assign to column D the position. Therefore for the entries for David should have assistant and Brian should have Supervisor in the 4th column table 1. Any help would be greatly appreciated. Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Returning a text string after a 2 criteria search
Perfect. Thank you very much.
"Bob Phillips" wrote: I forgot to mention that it is an array formula, so you need to Ctrl-Shift-Enter after entering the formula, not just Enter. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Spag" wrote in message ... Thanks Bob, I've put the formula in but am getting #N/A in return. I've made sure the dates are formatted correctly and have cross referenced to make sure that the MATCH"date" and MATCH"worker" are column and cell correct. Is there anything else I should be checking? "Bob Phillips" wrote: Assuming Table 1 is in A1:C20 and table 2 is in M1:O20 =INDEX($O$1:$O$20,MATCH(1,($M$1:$M$20=A1)*($N$1:$N $20=B1),0)) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Spag" wrote in message ... Hello, I have a list of workers that carried out multiple actions on a given day. What I wish to do is a apply the position they were assigned to on a given day to every action. I have a table of actions by date and worker and a table of positions by date and worker. For example: Table 1 Actions Brian 22/4 cleaning David 22/4 washing Brian 22/4 gardening David 22/4 running Sally 23/4 cooking Table 2 Position Brian 22/4 Supervisor David 22/4 Assistant Sally 23/4 Trainee In table 1 I wish to assign to column D the position. Therefore for the entries for David should have assistant and Brian should have Supervisor in the 4th column table 1. Any help would be greatly appreciated. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do a search for a text string using a formula | Excel Discussion (Misc queries) | |||
Text search within a string using formula | Excel Worksheet Functions | |||
Search, find or lookup defined text in text string | Excel Worksheet Functions | |||
Search string with multiple criteria | Excel Worksheet Functions | |||
Newbie: How to search a text string from right | Excel Worksheet Functions |