Morning all
Could anyone help me with a formula problem? I have a column (column C) of products that are alphanumeric. I'd like to put a formula in column B that looks up whether the value in column C contains "240", "230" OR "350" - not all of them but one or the other.
I have used the formula below, but is there a way I can nest it to search for the other two values?
=IF(ISERROR(SEARCH("240",C5)),"No","Yes")
Any help would be greatly appreciated.
Steve
Last edited by SAsplin; 07-15-2011 at 06:10 AM.
=IF(ISERROR(SEARCH("240",C5)),IF(ISERROR(SEARCH("230",C5)),IF(ISERROR(SEARCH("350",C5)),"No",350),230),240)
Does that work?
Hi
try this formula
=MATCH(TRUE,ISNUMBER(SEARCH({240,230,250},C5)),0)
it is an array formula and should be confirmed through Ctrl+Shift+Entre
than use if formula to get No or Yes
you could also define a range of above vaue i.e., 240,230,250 sepretly and give the that range in the above formula
Last edited by Azam Ali; 07-15-2011 at 05:50 AM.
Hi,
a possible solution
Regards=IF(SUMPRODUCT(--ISNUMBER(FIND({230,240,250},C5))),"Yes";"No")
How about this:
=IF(SUMPRODUCT(COUNTIF(C5,"*"&{230, 240, 350}&"*"))>0, "YES", "NO")
Thanks for that. Yes, this returns which are 230's, 240's and 350's - is there any way to just get a 'Yes'?
In any solution you choose: Instead of "NO" leave just quotations "". That will give you (nothing) between ""
Zbor, that formula's a great shout! Works like a charm.
Many thanks
Steve
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks