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

 

[Snakehips]

Then hopefully like below.
I have put the type of employment in H as a separate formula. The formula in I1 references H1 three times. If you want to have a single formula then edit I1 and replace H1 with the formula in H1.

Book1
	A	B	C	D	E	F	G	H	I
1	Other Stuff	Member	Type Of Employment	Name			Dylan	Seasonal or Temp	Category2
2	abc	Yes	Part Time	Bob
3	999	Yes	Full Time	Mary
4	xyz	Yes	Spare Part	Woody
5	abc	No	Seasonal or Temp	Charlie
6	123	No	Casual	Sid
7	qwpp	Not Sure		Betty
8	XXXX	No	Seasonal or Temp	Dylan
Sheet1

Cell Formulas
Range	Formula
H1	H1	=INDEX(A:D,MATCH(G1,INDEX(A:D,,MATCH("Name",$A$1:$D$1,0)),0),MATCH("Type Of Employment",$A$1:$D$1,0))
I1	I1	=IF(OR(ISNUMBER(SEARCH("part",H1,1)),ISNUMBER(SEARCH("seasonal",H1,1))),"Category2",IF(ISNUMBER(SEARCH("full",H1,1)),"Category1","None"))

 

[tropics123]

Then hopefully like below.
I have put the type of employment in H as a separate formula. The formula in I1 references H1 three times. If you want to have a single formula then edit I1 and replace H1 with the formula in H1.

Book1
	A	B	C	D	E	F	G	H	I
1	Other Stuff	Member	Type Of Employment	Name			Dylan	Seasonal or Temp	Category2
2	abc	Yes	Part Time	Bob
3	999	Yes	Full Time	Mary
4	xyz	Yes	Spare Part	Woody
5	abc	No	Seasonal or Temp	Charlie
6	123	No	Casual	Sid
7	qwpp	Not Sure		Betty
8	XXXX	No	Seasonal or Temp	Dylan
Sheet1

Cell Formulas
Range	Formula
H1	H1	=INDEX(A:D,MATCH(G1,INDEX(A:D,,MATCH("Name",$A$1:$D$1,0)),0),MATCH("Type Of Employment",$A$1:$D$1,0))
I1	I1	=IF(OR(ISNUMBER(SEARCH("part",H1,1)),ISNUMBER(SEARCH("seasonal",H1,1))),"Category2",IF(ISNUMBER(SEARCH("full",H1,1)),"Category1","None"))

@Snakehips
AWESOME! It worked wonderfully!! I combined the two formulas because that was exactly what I was looking for. Thank you so much for showing the formulas separately and explained how to combine them....that was the step I was missing. This will save me so much time. Thanks again!

 

[Snakehips]

Great stuff! Happy to have helped.