# How many values can be in IF(ISNumber(search

1. ## 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 -
Allen

2. ## 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", "")

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