Excel IF formula with various conditions

[tropics123]

Hi, any advice to get this formula to work is greatly appreciated. Assuming the header "Name" starts on cell A1.

Here is what I'm trying to do. For example, let's look at the employee named Bob. If his "Type of Employment" (column C) is:
1) Part time then the result will say "Category 2"
2) Seasonal then the result will say "Category 2"
3) Full time then the result will say "Category 2"
4) If it's none of those then the result will say "None"

Here's my formula. I only want to look up partial word such as "part" or "full" or "seasonal". At one point, the answer was showing "None" no matter what and then I tweaked it some more and now it doesn't work.

=IF(INDEX(A2:C5,MATCH("Bob",A2:A5,0),MATCH("Type of Employment",A1:C1,0))="*part*","Category 2",INDEX(A2:C5,MATCH("Bob",A2:A5,0),MATCH("Type of Employment",A1:C1,0))="*seasonal*","Category 2",INDEX(A2:C5,MATCH("Bob",A2:A5,0),MATCH("Type of Employment",A1:C1,0))="*full*","Category 1","None")

Name	Member	Type of Employment
Bob	Yes	Part time
Mary	Yes	Full time
Woody	Yes	Part time
Dylan	No	Seasonal or Temp

 

[tyija1995]

Hey, is this something that you're looking to achieve?

CheaperWasteMappingDocument.xlsx
	A	B	C	D	E
1	Name	Member	Type of Employment	O365 Formula	Other Method
2	Bob	Yes	Part time	Category 2	Category 2
3	Mary	Yes	Full time	None	None
4	Woody	Yes	Part time	None	None
5	Dylan	No	Seasonal or Temp	None	None
Sheet6

Cell Formulas
Range	Formula
D2:D5	D2	=IF(AND(A2="Bob",OR(ISNUMBER(SEARCH({"part","full","seasonal"},C2)))),"Category 2","None")
E2:E5	E2	=IF(AND(A2="Bob",OR(ISNUMBER(SEARCH("part",C2)),ISNUMBER(SEARCH("full",C2)),ISNUMBER(SEARCH("seasonal",C2)))),"Category 2","None")

I think the formula in column D needs O365 but not 100% sure on that! But E should be usable for most versions.

 

[tropics123]

Hey, is this something that you're looking to achieve?

CheaperWasteMappingDocument.xlsx
	A	B	C	D	E
1	Name	Member	Type of Employment	O365 Formula	Other Method
2	Bob	Yes	Part time	Category 2	Category 2
3	Mary	Yes	Full time	None	None
4	Woody	Yes	Part time	None	None
5	Dylan	No	Seasonal or Temp	None	None
Sheet6

Cell Formulas
Range	Formula
D2:D5	D2	=IF(AND(A2="Bob",OR(ISNUMBER(SEARCH({"part","full","seasonal"},C2)))),"Category 2","None")
E2:E5	E2	=IF(AND(A2="Bob",OR(ISNUMBER(SEARCH("part",C2)),ISNUMBER(SEARCH("full",C2)),ISNUMBER(SEARCH("seasonal",C2)))),"Category 2","None")

I think the formula in column D needs O365 but not 100% sure on that! But E should be usable for most versions.

Hi @tyija1995 , if the result for "Type of Employment" is "Full time", then the answer needs to show "Category 1". Sorry, I typed wrong the first time.

Here is what I'm trying to do. For example, let's look at the employee named Bob. If his "Type of Employment" (column C) is:
1) Part time then the result will say "Category 2"
2) Seasonal then the result will say "Category 2"
3) Full time then the result will say "Category 1" <- typo ed
4) If it's none of those then the result will say "None"

 

[tyija1995]

Here is what I'm trying to do. For example, let's look at the employee named Bob. If his "Type of Employment" (column C) is:
1) Part time then the result will say "Category 2"
2) Seasonal then the result will say "Category 2"
3) Full time then the result will say "Category 2"
4) If it's none of those then the result will say "None"

Hey, so is this initial post you put a bit of a typo then? As now you're saying full time should result in "Category 1"?

 

[tropics123]

Hey, so is this initial post you put a bit of a typo then? As now you're saying full time should result in "Category 1"?

Sorry, I typed wrong. Please see my most recent correction. Yes, "Category 1". Sorry about the confusion.

 

[tyija1995]

Sorry, I typed wrong. Please see my most recent correction. Yes, "Category 1". Sorry about the confusion.

OK that's not a problem, also are your answers going to be different based on the person? Or is it always category 1 for "full time" regardless of whether it is "Bob" or not?

 

[tropics123]

OK that's not a problem, also are your answers going to be different based on the person? Or is it always category 1 for "full time" regardless of whether it is "Bob" or not?

Hi @tyija1995, yes the answer will be different. In my original sheet, the formula is reading from cell B2 and whatever name that goes into that cell. It could be Dylan then the result should be Category 2. And if it's Mary, then result should be Category 1. Hope that makes sense.

 

[tyija1995]

OK so just to summarise, if a member is full time then they are category 1, otherwise category 2? Is this correct?
Then if part time/seasonal member is category 2, what about non-member in this case? Just need to clarify this criteria.

 

[tropics123]

OK so just to summarise, if a member is full time then they are category 1, otherwise category 2? Is this correct?
Then if part time/seasonal member is category 2, what about non-member in this case? Just need to clarify this criteria.

That is correct. However, if there's a new employee "Betty" and let's say HR forgets to enter Betty's info in "Type of Employment" and either left it blank or puts in "N/A", then the answer should say "None" because it doesn't belong in either Category 1 or 2.

 

[tyija1995]

OK so something like this if I have understood criteria correctly at this point...

CheaperWasteMappingDocument.xlsx
	A	B	C	D
1	Name	Member	Type of Employment	Formula
2	Bob	Yes	Part time	Category 2
3	Mary	Yes	Full time	Category 1
4	Woody	Yes	Part time	Category 2
5	Dylan	No	Seasonal or Temp	Category 1
6	Betty	Yes		None
Sheet6

Cell Formulas
Range	Formula
D2:D6	D2	=IF(AND(B2="Yes",OR(ISNUMBER(SEARCH({"part","seasonal"},C2)))),"Category 2",IF(AND(B2="Yes",ISNUMBER(SEARCH("full",C2))),"Category 1",IF(AND(B2="No",OR(ISNUMBER(SEARCH({"part","seasonal"},C2)))),"Category 1",IF(AND(B2="No",ISNUMBER(SEARCH("full",C2))),"Category 2","None"))))