How to return 'string A' if 'cell range X' contains 'strings B, C, ...' and other strings for other conditions?

[MrInacio]

Hello people,

So, I have a little experience in programming and (very) little experience with Excel.

What I want to do is to have a multiple condition check in a single cell, so it returns one string if a range of cells contains specific other strings, another string if it contains another set of specific strings, and so forth.

An example to make it clearer:

Let's say the purpose of this is to schedule different types of meetings according to people's availability.
There are three types of meetings: Brainstorming, Finances and Marketing.
For Brainstorming we need Annie, Bart and Carol. For Finances we need Diana, Frank and George. For Marketing we need Annie, Diana and Hudson.

Say we have a sheet in which a range of cells represents "Monday 3rd", another represents "Tuesday 4th" and so on; in those, everyone who's available on that day has their name on it.

So, on another sheet, we'd have a formula that'd check who's available on the "Monday 3rd" by checking whose names are there. If Annie, Bart and Carol are available, we could have a Brainstorming meeting. If, otherwise, Diana, Frank and George are available, it could be a Finances one. If all six of them are available, could be either one.

Does anyone have any idea on how to do this on Excel?

 

[JustynaMK]

Hi & welcome to the forum.

You can create a following setup:

1. In cells A2 and below (A3, A4...) you have your dates (03/06/2019, 04/06/2019...).

2. in cells B2-H2 you enter the names of people that can attend the meeting> This can be entered in any random order (e.g. B2 = "Annie", C2 = "Carol", D2 = "Hudson", E2 = "Bart"). The range ends with column H cause you mentioned a maximum of 7 people whose attendance will be analysed.

3. In cells I1-L1 we're going to add our headers (I2 = "Brainstorming", J2 = "Finances", K2 = "Marketing", L2 = "All").

4. Now you need to enter the following formulas into columns I-L (row 2 - drag it down if needed):
Cell I2 ->

=IF(SUMPRODUCT(IF(ISNUMBER(MATCH({"Annie","Bart","Carol"},$B2:$H2,0)),1,0))=3,"x","")

Cell J2 ->

=IF(SUMPRODUCT(IF(ISNUMBER(MATCH({"Diana","Frank","George"},$B2:$H2,0)),1,0))=3,"x","")

Cell K2 ->

=IF(SUMPRODUCT(IF(ISNUMBER(MATCH({"Annie","Diana","Hudson"},$B2:$H2,0)),1,0))=3,"x","")

Cell L2 ->

=IF(COUNTIF($I2:$K2,"x")=3,"x","")

Result:

Hope it helps.

 

[MrInacio]

Works perfectly.

I ended up using Sums and Countifs, but your idea kicked off the stuff.

Thank you very much.

 

[JustynaMK]

glad you figured it out. Take care!