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

 

[tropics123]

=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"))))

That worked! Thank you @tyija1995. If down the road, we decide that column B "Member" is unnecessary and want to remove the column, then do I remove the part of the formula before OR (B2="Yes",OR)?

 

[tyija1995]

That's correct, you would no longer need the AND statement if you were to remove that part of the criteria from the formula, I used it to differentiate whether a person is category 1 or category 2, as member full time was cat 1, member part time cat 2, etc.

 

[Snakehips]

Will this not suffice?

Book1
	A	B	C	D
1	Name	Member	Type Of Employment
2	Bob	Yes	Part Time	Category2
3	Mary	Yes	Full Time	Category1
4	Woody	Yes	Spare Part	Category2
5	Dylan	No	Seasonal or Temp	Category2
6	Sid	No	Casual	None
7	Betty			None
8
Sheet1

Cell Formulas
Range	Formula
D2:D7	D2	=IF(OR(ISNUMBER(SEARCH("part",C2,1)),ISNUMBER(SEARCH("seasonal",C2,1))),"Category2",IF(ISNUMBER(SEARCH("full",C2,1)),"Category1","None"))

 

[tropics123]

That's correct, you would no longer need the AND statement if you were to remove that part of the criteria from the formula, I used it to differentiate whether a person is category 1 or category 2, as member full time was cat 1, member part time cat 2, etc.

Thank you very much for your help!

 

[tropics123]

Will this not suffice?

Book1
	A	B	C	D
1	Name	Member	Type Of Employment
2	Bob	Yes	Part Time	Category2
3	Mary	Yes	Full Time	Category1
4	Woody	Yes	Spare Part	Category2
5	Dylan	No	Seasonal or Temp	Category2
6	Sid	No	Casual	None
7	Betty			None
8
Sheet1

Cell Formulas
Range	Formula
D2:D7	D2	=IF(OR(ISNUMBER(SEARCH("part",C2,1)),ISNUMBER(SEARCH("seasonal",C2,1))),"Category2",IF(ISNUMBER(SEARCH("full",C2,1)),"Category1","None"))

Hi @Snakehips, this works perfectly! Thank you very much!

 

[tropics123]

@tyija1995 and @Snakehips, just curious how this formula works if you guys have time to explain so I can understand better the use of ISNUMBER. I thought that formula was mainly used for checking if the cell has a number or not.

 

[tyija1995]

@tyija1995 and @Snakehips, just curious how this formula works if you guys have time to explain so I can understand better the use of ISNUMBER. I thought that formula was mainly used for checking if the cell has a number or not.

You're correct, ISNUMBER results in either true or false, it even converts errors to false, which is a powerful thing to do with SEARCH function.

Then in the IF statement, the parameter outputs are based on value_if_true and value_if_false, so you can use it to full advantage here.

 

[tropics123]

In the excitement of the formula working, I forgot about the original formula I started with INDEX & MATCH. To make this more complex, the actual sheet I'm pulling info from is massive and I'm pulling info from that sheet to another sheet, but for the simplicity of this, we can pretend everything is on one sheet. I don't always know the column header position of "Name" and I don't know the column position of "Type of Employment" because they could change, depending on the file we receive and that's the reason why I used INDEX & MATCH. I tried using the formula you gave me and incorporated INDEX & MATCH and I can't get it to work. Since you guys @tyija1995 & @Snakehips were so nice to help before, would you have time to take a peak at my formula below and see why it's not working?

Cell G1 from the formula below indicates the name field since we could be looking info for Bob, Betty, or Dylan.

=IF(or(ISNUMBER(INDEX(A2:C7,MATCH(G1,$A:$A,0),search("part",MATCH("Type of Employment",A1:D1,0),1),ISNUMBER(search("seasonal",MATCH("type of employment",a1:d1,0)1),"Category 2",IF(ISNUMBER(SEARCH,MATCH("full",a1:d1,0)1),"Category 1","None"))

 

[Snakehips]

@tropics123 Is this a lookup, from the big listing in the second sheet, of a single name that you enter in G1, in order to lookup the necessary info to compute the category?
If so, in which cell do you want category returned to?
If it is more than that then please explain clearly.

 

[tropics123]

@tropics123 Is this a lookup, from the big listing in the second sheet, of a single name that you enter in G1, in order to lookup the necessary info to compute the category?
If so, in which cell do you want category returned to?
If it is more than that then please explain clearly.

@Snakehips
Yes, this is a lookup. I am looking up on a big spreadsheet called "Vendor" and pulling info into my sheet called "Main". Cell G1 would be on "Main" sheet and the person's name will vary, depending on who I'm trying to lookup so it could be Bob, Mary, Betty, Woody. If I type in the name Betty in cell G1, then in cell B52 on "Main" sheet it will give me the result I need. Hopefully, I answered your question.