Multiple Ifs, Ands, and Ors in one formula

[Chevon]

I am trying to make one formula that can incorporate many ifs, ands, and ors. Here are my individual statements:
If D13 or H13 is “Lead” or “Lead-lined” then K13 should be “Lead”

If D13 and H13 contain “Non-Lead” then K13 should be “Non-Lead”

If D13 and H13 contain “Unknown” then K13 should be “Unknown”

If D13 contains “Unknown” and H13 contains “Non-Lead” then K13 should be “Unknown”

If H13 contains “Unknown” and D13 contains “Non-Lead” then K13 should be “Unknown”

 

[S Oberlander]

Try

=IF(left(D13,8)&left(H13,8)=“Non-LeadNon-Lead”,“Non-Lead”,"Unknown")

 

[S Oberlander]

Sorry I missed your first situation
Modified formula:

=switch(left(D13,8)&left(H13,8),“Non-LeadNon-Lead”,“Non-Lead”,"LeadLead-lin","Lead","Lead-linLead","Lead","Unknown")

 

[6StringJazzer]

Sorry I missed your first situation
Modified formula:

=switch(left(D13,8)&left(H13,8),“Non-LeadNon-Lead”,“Non-Lead”,"LeadLead-lin","Lead","Lead-linLead","Lead","Unknown")

Don't use "smart quotes" in formulas or code. Excel won't recognize them as quotes.

$scratch.xlsm
	D	E	F	G	H	I	J	K
1							Desired Result	Formula Result
2	Lead						Lead	Lead
3					Lead		Lead	Lead
4	Lead-lined						Lead	Lead
5					Lead-lined		Lead	Lead
6	Non-Lead				Non-Lead		Non-Lead	Non-Lead
7	Unknown				Unknown		Unknown	Unknown
8	Unknown				Non-Lead		Unknown	Unknown
9	Non-Lead				Unknown		Unknown	Unknown
Sheet16

Cell Formulas
Range	Formula
K2:K9	K2	=IF(AND(D2="Non-Lead",H2="Non-Lead"),"Non-Lead",IF(OR(D2="Lead",H2="Lead",D2="Lead-Lined",H2="Lead-Lined"),"Lead","Unknown"))

 

[6StringJazzer]

Sorry, I provided a formula before I opened your screenshot. I did not realize that they keywords were only part of the cell text. Can you paste in the actual data instead of a picture?

 

[S Oberlander]

Don't use "smart quotes" in formulas or code. Excel won't recognize them as quotes.

Not sure why the system put different type of quotes, I used the same button on my keyboard for all.
That being said, I tested my own formula in excel now and modified it to work properly here:

=SWITCH(LEFT(D13,8)&LEFT(H13,8),"Non-LeadNon-Lead","Non-Lead","LeadLead-lin","Lead","Lead-linLead","Lead","Unknown")

 

[Chevon]

THANK YOU