+ Reply to Thread
Results 1 to 2 of 2

How many values can be in IF(ISNumber(search

  1. #1
    Registered User
    Join Date
    United States
    MS-Off Ver
    Excel 2007

    How many values can be in IF(ISNumber(search

    Hello -

    I'm trying to see if several words/values are in a cell, but am not sure how many different word/value options can be nested in the statement? Here is one of the formulas I'm using;

    =IF(ISNUMBER(SEARCH({"condition","damaged box","separate box","envelope","ripped","damage","tear","crushed","packaging","one box","worn out","worn","opened"},B107)),"condition")

    This one has 13 different words/phrases and is pretty normal for how many I would have. Is this the correct way or would I need to do seperate "IF(Isnumber(search" formula's for each word/value?

    Thanks -

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365

    Re: How many values can be in IF(ISNumber(search

    This works and has the benefit of being easy to understand and edit.

    You could list your words in a table, then change your formula to an array that cycles through all the options in the table looking for any match.

    1) Create a sheet called LISTS, put the cells A1:A13 with your list.
    2) Use this array formula to test cell B107 against Lists!A1:A14

    =IF(OR(ISNUMBER(MATCH("*" & Lists!$A$1:$A$13 & "*", B107, 0))), "condition", "")

    ...enter this into a cell by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula.

    Increase the "neato" factor by using a self-expanding names range for your list of words.

    1) Press CTRL-F3 to open the Name Wizard
    2) Create a New name called MyWords that RefersTo: =OFFSET(List!$A$1, , , COUNTA(List!$A:$A), )

    3) Now you can put as many words in sequential cells going down column A and you will now have to edit this formula, it will keep working:

    =IF(OR(ISNUMBER(MATCH("*" & MyWords & "*", B107, 0))), "condition", "")
    Last edited by JBeaucaire; 02-25-2013 at 11:15 PM.
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1