Returning a text string after a 2 criteria search

S

Spag

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
 
B

Bob Phillips

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)
 
S

Spag

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?
 
B

Bob Phillips

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)
 
S

Spag

Perfect. Thank you very much.

Bob Phillips said:
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)
Click to expand...
 
Top