Reputation: 337
What I'm trying to do is create a spreadsheet that has a list of staff and clients, and tells you if a staff person has met that client before. Each staff person has a list of clients underneath their name and at the top of the spreadsheet there's two drop-down boxes where somebody can select a staff person, and a client, then under the status heading it will say whether the two have met before.
I've only been able to get the formula to process one column/staff person, but I get errors when I try to include more columns. Here's my formula for the "Status" cell;
=IF(COUNTIF(C$21:C$455,D5),"Have Met","Have Not Met")
If I add multiple IF statements to the formula e.g below I get a #VALUE! error. So I'm doing something wrong..
=IF(COUNTIF(C$21:C$455,D5),"Have Met","Have Not Met"),IF(COUNTIF(D$21:D$455,D5),"Have Met","Have Not Met")
Sorry I accidentally cut off the column letters and row numbers from the image. Staff column is "C", Client is "D" etc. Staff Header row is "20", First client row is "21" etc.
Is there a better formula I should be using?
Upvotes: 0
Views: 157
Reputation: 84475
Here is a different way which also handles unknown staff and client. You would adjust range as appropriate.
=IF(ISERROR(MATCH(D5,INDEX($C$20:$E$26,,MATCH($C$5,$C$20:$E$20,0)),0)),"Have Not met","Have Met")
Upvotes: 1
Reputation: 37155
Use Countif()
function with combination of Index()
and Match()
function.
=IF(COUNTIF(INDEX($C$20:$E$26,,MATCH($C$5,$C$20:$E$20,0)),$D$5)>0,"Have Met","Have Not Met")
You may need to adjust the formula in case of you if your data in more ranges.
Upvotes: 2