Finding text (A) or text (B) and returning text depending on what was found

[StephR]

Hi! I'm attempting to create a formula that will search an export of our memos for "% of" or "spend" and return "Fee" AND search for "Direct Bill" or "Fronted" and return "Pass-through". If neither of those are present I would like it to return "Check manually". Any idea if this is possible? The goal is to be able to filter by "Fee" or "Pass-through" for the past few years of data.

	Date	Transaction Type	Num	Customer	Memo/Description	Fee / Pass-Through
Product
	1/1/2020	Invoice	123	ABC	Product - 5.499% of $100,000
	2/1/2020	Invoice	234	ABC	Product Spend - 5.5% of $50,000
	3/1/2020	Invoice	345	ABC	Product Test
	4/1/2020	Invoice	456	ABC	Product Test - Direct Bill

 

[kweaver]

Welcome to Mr. Excel,

Is this what you mean?

Book1
	A	B	C	D	E	F	G
1		Date	Transaction Type	Num	Customer	Memo/Description	Fee / Pass-Through
2	Product
3		1/1/2020	Invoice	123	ABC	Product - 5.499% of $100,000	Fee
4		2/1/2020	Invoice	234	ABC	Product Spend - 5.5% of $50,000	Fee
5		3/1/2020	Invoice	345	ABC	Product Test	Check Manually
6		4/1/2020	Invoice	456	ABC	Product Test - Direct Bill	Pass-Through
7						Test Fronted	Pass-Through
Sheet1

Cell Formulas
Range	Formula
G3:G7	G3	=IF(OR(ISNUMBER(SEARCH("Spend",F3)),ISNUMBER(SEARCH("%",F3))),"Fee",IF(OR(ISNUMBER(SEARCH("Direct Bill",F3)),ISNUMBER(SEARCH("Fronted",F3))),"Pass-Through","Check Manually"))

 

[StephR]

Welcome to Mr. Excel,

Is this what you mean?

Book1
	A	B	C	D	E	F	G
1		Date	Transaction Type	Num	Customer	Memo/Description	Fee / Pass-Through
2	Product
3		1/1/2020	Invoice	123	ABC	Product - 5.499% of $100,000	Fee
4		2/1/2020	Invoice	234	ABC	Product Spend - 5.5% of $50,000	Fee
5		3/1/2020	Invoice	345	ABC	Product Test	Check Manually
6		4/1/2020	Invoice	456	ABC	Product Test - Direct Bill	Pass-Through
7						Test Fronted	Pass-Through
Sheet1

Cell Formulas
Range	Formula
G3:G7	G3	=IF(OR(ISNUMBER(SEARCH("Spend",F3)),ISNUMBER(SEARCH("%",F3))),"Fee",IF(OR(ISNUMBER(SEARCH("Direct Bill",F3)),ISNUMBER(SEARCH("Fronted",F3))),"Pass-Through","Check Manually"))

Yes, that worked! Thank you so much!

 

[Peter_SSs]

A shorter way to write basically the same thing:

21 06 09.xlsm
	A	B	C	D	E	F	G
1		Date	Transaction Type	Num	Customer	Memo/Description	Fee / Pass-Through
2	Product
3		1/01/2020	Invoice	123	ABC	Product - 5.499% of $100,000	Fee
4		2/01/2020	Invoice	234	ABC	Product Spend - 5.5% of $50,000	Fee
5		3/01/2020	Invoice	345	ABC	Product Test	Check manually
6		4/01/2020	Invoice	456	ABC	Product Test - Direct Bill	Pass-through
7						Test Fronted	Pass-through
Fee or Pass

Cell Formulas
Range	Formula
G3:G7	G3	=IF(COUNT(SEARCH({"spend","%"},F3)),"Fee",IF(COUNT(SEARCH({"direct bill","fronted"},F3)),"Pass-through","Check manually"))

 

[Phuoc]

Or try this in G3:

=LOOKUP(1000,SEARCH({"?*","spend","%","direct bill","fronted"},F3),{"Check manually","Fee","Fee","Pass-through","Pass-through"})