IS NUMBER SEARCH with TWO conditions

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
644
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.

[TABLE="width: 285"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Mechanic[/TD]
[TD]Conversion [/TD]
[/TR]
[TR]
[TD]Now £2[/TD]
[TD]Fixed Price Discount[/TD]
[/TR]
[TR]
[TD]6 for 4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6 for £4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3 for 2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3 for £2[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
try

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

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top