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

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

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

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

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