IF / SEARCH function not progressing to next condition?

[suzid123]

Hi, I'm almost certain I'm using the wrong search function. Apologies, my work computer doesn't permit me to install XL2BB.

I have one list which has classes and their status in the program.
The possible statuses are:
Active
Confirmed
Pending
Exploratory

There are multiple classes per school, and each class can have a different status.

I want a different column to assign a category to the school, using the hierarchy of terms above (Active is first, then Confirmed, etc

So if School A's classes read:
Confirmed, Active, Pending

I want Cell F2 to read "Active".


I have tried creating an =TEXTJOIN range, so the results come up as "Confirmed,Active,Pending", but I can't do a wildcard search with IF function, and my SEARCH function doesn't progress past the first logic test.

Thanks in advance for any help.

 

[StephenCrump]

Welcome to the Forum!

Like this perhaps?

	A	B	C	D	E
1	Status
2	Active
3	Confirmed
4	Pending
5	Exploratory
6
7
8	School A	Confirmed		School A	Active
9	School A	Active		School B	Confirmed
10	School A	Pending		School C	Active
11	School B	Confirmed		School D	Exploratory
12	School B	Pending		School E	Pending
13	School B	Confirmed
14	School C	Active
15	School C	Exploratory
16	School D	Exploratory
17	School E	Exploratory
18	School E	Pending
19
Sheet1

Cell Formulas
Range	Formula
E8:E12	E8	=INDEX(Status,MIN(FILTER(MATCH(B$8:B$18,Status,),A$8:A$18=D8)))

Named Ranges
Name	Refers To	Cells
Status	=Sheet1!$A$2:$A$5	E8:E12

 

[suzid123]

Welcome to the Forum!

Like this perhaps?

	A	B	C	D	E
1	Status
2	Active
3	Confirmed
4	Pending
5	Exploratory
6
7
8	School A	Confirmed		School A	Active
9	School A	Active		School B	Confirmed
10	School A	Pending		School C	Active
11	School B	Confirmed		School D	Exploratory
12	School B	Pending		School E	Pending
13	School B	Confirmed
14	School C	Active
15	School C	Exploratory
16	School D	Exploratory
17	School E	Exploratory
18	School E	Pending
19
Sheet1

Cell Formulas
Range	Formula
E8:E12	E8	=INDEX(Status,MIN(FILTER(MATCH(B$8:B$18,Status,),A$8:A$18=D8)))

Named Ranges
Name	Refers To	Cells
Status	=Sheet1!$A$2:$A$5	E8:E12

Hi, thanks so much for your response. For some reason I just got a "#VALUE!" Error.

I saved the Status range on another sheet, it references appropriately,
These are my actual & Exact ranges as of right now. I probably need the G ranges to be longer. Any idea why it would have returned a #value! ?

=INDEX(Status,MIN(FILTER(MATCH('[2022 Participating Stakeholders.xlsx]Actual '!$G3:$G50,Status,),D6:D1164=D6)))

 

[StephenCrump]

My formula: =INDEX(Status,MIN(FILTER(MATCH(B$8:B$18,Status,),A$8:A$18=D8))), i.e. equal sized column references

Your formula: =INDEX(Status,MIN(FILTER(MATCH('[2022 Participating Stakeholders.xlsx]Actual '!$G3:$G50,Status,),D6:D1164=D6))), i.e. mismatched sizes.

I think this is probably the source of the #VALUE error.

By the way, do you need the workbook reference: '[2022 Participating Stakeholders.xlsx]Actual '!$G3:$G50.

And is the worksheet name 'Actual ' (with a trailing space) or Actual?

 

[suzid123]

My formula: =INDEX(Status,MIN(FILTER(MATCH(B$8:B$18,Status,),A$8:A$18=D8))), i.e. equal sized column references

Your formula: =INDEX(Status,MIN(FILTER(MATCH('[2022 Participating Stakeholders.xlsx]Actual '!$G3:$G50,Status,),D6:D1164=D6))), i.e. mismatched sizes.

I think this is probably the source of the #VALUE error.

By the way, do you need the workbook reference: '[2022 Participating Stakeholders.xlsx]Actual '!$G3:$G50.

And is the worksheet name 'Actual ' (with a trailing space) or Actual?

Hiya, thanks so much, fixing the range sizes worked perfectly!

And yes, the 'Participating Stakeholders' sheet reference was because it was drawing from a second sheet - I believe they do have a trailing space in their sheet name, but as they have other formulas drawing from the sheet I daren't change it haha

THANK YOU!

 

[StephenCrump]

Great, I'm glad you got it working.