Complicated ?? IF/AND Formula Question

C

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
 
J

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
 
S

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Comparing 2 Tables 0
Double unique formula 5
Copy and shift data automaticaly 0
Arrays and Average 3
Sum if two conditions met 3
Avg Arrays 2
Comparing 2 Tables 0
If Formula and Dates 4

Top