Lookup for word in company name

Closed
Report
Posts
1
Registration date
Monday May 24, 2010
Status
Member
Last seen
May 26, 2010
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,


I want to do a "Match" or "Lookup" for a word in a cell. For example I have 1,000 company names. I want to label them into type of company. So for all the companies that have Mortgage or Loan in the name of the business, I want to enter in the next coulmn "Mortgage". So ABC Loan co., Best Loans, Title Mortgage, Loan Comapany of America - "Mortgage" Same for Accounting in the name - Accounting or any other industry.

Help!

The answer given works and really helped:
=IF(AND(ISERROR(SEARCH("loan",F20)),ISERROR(SEARCH("mortgage",F20))),"","mortgage")

Next question:

=IF(AND(ISERROR(SEARCH("loan",F20)),ISERROR(SEARCH("mortgage",F20))),"","mortgage")

=IF(AND(ISERROR(SEARCH("ACCOUNTING",E21)),ISERROR(SEARCH("ACCOUNTING",E21))),"","ACCOUNTING")

=IF(AND(ISERROR(SEARCH("COMPUTER",E21)),ISERROR(SEARCH("COMPUTER",E21))),"","IT")

How do I combine more then one criteria?



1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
What you mean by "combine more then one criteria"

if you mean by do if A is true AND B is true, then your first formula is example of that

AND(cond1, cond2, ...)
OR(cond1, cond2,...)

If you meant nested if then

=IF( cond, TT, FF)

where if cond is found to be true it looks at TT
and if cond is found to be false, it looks at FF

now FF and TT can be values or it can be formula or it can be other IFstatement

=IF(COND, "Apple", "Orange") 'values

=IF(COND, TODAY(), A1 * B1) 'formula

=IF(COND, IF(COND2, "Apple", "Orange"), FF) 'nested if

You can have upto 7 nested IFs