Complicated ?? IF/AND Formula Question

[carl]

I have 2 Tables and am trying to use them to tell me if
my saleteam is selling the products that they have been
assigned too.

Table 1 has my list of products in rows and list of
SalesPeople in Columns. The table identifies (cell entry
=
1) which Salespeople are assigned to each product.

Table 2 has my list of products in rows and the
Salepeople
that have sold any of the products in Columns. The table
identifies how much each salesperson sold of each product.

Table1
Product JB JW
AIG
TWX 1 1
ABT 1
ALL 1
AXP 1 1

Table2
Product JB JW
AIG
TWX 12
ABT 12
ALL 12
AXP 12 12

I would like to create a table that would tell me IF the
Salesperson is Assigned to the Product, IF he sold any.
If the answer is yes, return OK. If the answer is no,
return CHECK.

Perhaps the 3rd table would look like this...

Product JB JW
AIG OK OK
TWX OK CHECK
ABT OK OK
ALL CHECK CHECK
AXP OK OK

Thank you in advance

 

[Jason Morin]

If table 1 is in A1:C6, table 2 in A8:C13, and the new
table is in A15:C20, put this formula in B16 and fill
across and down:

=IF(ISNUMBER(INDEX($A$1:$C$6,MATCH($A16,$A$1:$A$6,0),MATCH
(B$15,$A$1:$C$1,0)))=ISNUMBER(INDEX($A$8:$C$13,MATCH
($A16,$A$8:$A$13,0),MATCH
(B$15,$A$8:$C$8,0))),"OK","Check")

When inserting the formula, watch the wrap in the formula
bar.

HTH
Jason
Atlanta, GA

 

[sulprobil]

Hmm, I would suggest to put in B16:

=IF(B2,IF(B9>0,"Ok","Check"),"")

You would not check a value if a person is not assigned to
that product, right?

Regards,
sulprobil