IS NUMBER SEARCH with TWO conditions

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
637
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi

I've got a formula which searches for specific text in a cell, then returns a phrase if that text is in the cell.

In the example below, if the word "Now" appears in the text in cell A1, then the phrase "Fixed Price Discount" will appear in cell B1.

The formula I have used is =IF(ISNUMBER(SEARCH("Now",A2)),"Fixed Price Discount","")

However, I'd like to know if it's possible to use this formula to search for TWO conditions.

Eg if the word "for" and the currency sign "£" appears in a cell, then I'd want the formula to return the phrase "Fixed price multibuy." If the word "for" appears in a cell BUT the currency sign "£" DOES NOT appear in the cell, then I'd want the phrase "Cheapest free" to appear in the corresponding cell. Is that possible? I'd prefer to avoid using IF(AND as there are dozens of combinations like the "6 for x" and "6 for £x" described below eg "3 for 2" and "3 for £2" and I'll run out of space! Excel can only take 64 arguments in a nested IF!

Thanks in advance.

MechanicConversion
Now £2Fixed Price Discount
6 for 4
6 for £4
3 for 2
3 for £2

<colgroup><col><col></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,185
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
try

Code:
=IF(ISNUMBER(SEARCH("for",A2)),IF(ISNUMBER(SEARCH("£",A2)),"Fixed price multibuy.","Cheapest free"),IF(ISNUMBER(SEARCH("Now",A2)),"Fixed Price Discount",""))
 

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
637
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Ok, great. Thanks Alan.
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,185
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
you're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,109,325
Messages
5,528,023
Members
409,799
Latest member
mlewan_ca

This Week's Hot Topics

Top