Excel formula for multiple criteria

[umang2382]

Hi,

I've been working on creating a single (or minimalist) formula for my spreadsheet.
Basically, what I've been looking to do is:
if B1 contains wildcard values one*, *two, three*, *four, then A1 = "low".
if B1 contains wildcard values five*, *six, *seven, eight*, then A1 = "
if B1 contains wildcard values nine*, *ten, *eleven, twelve*, then A1 = "high"

this would then be copied down to all subsequent cells in the same column (containing 8000+ rows)

I've been unsuccessful in finding a clean way of getting this done. Any help would be much appreciated.

[Pete_UK]

Try it like this in A1:

=IF(ISNUMBER(MATCH(B1,{"one*","*two","three*","*four"},0)),"low",IF(ISNUMBER(MATCH(B1,{"five*","*six","*seven","eight*"},0)),"",IF(ISNUMBER(MATCH(B1,{"nine*","*ten","*eleven","twelve*"},0)),"high","not found")))

You can change the "not found" to any other message (or just ""), then copy down.

Hope this helps.

Pete

[umang2382]

everything except the wildcard (*) seems to be working. With asterisk, it just ends up defaulting to "not found"

[FDibbins]

what would some samples be of what is baing matched with the wild card?

[umang2382]

I'd like to add all of these to the match criteria

ap650
ap6532
*lo
CP-7906G
CP-7911G
CP-7925G
CP-7931G
CP-7941G
CP-7942G
CP-7945G
CP-7961G
CP-7962G
CP-7965G
CP-7970G
CP-7975G
HP Jetdirect
*ilo
IOS*
Lexmark*
*esx*
ctx
NPI*
OA*
ZA*

[umang2382]

ok I had to flip the match cases from Pete_UK's repsonse. Once I did that it worked as expected.

=IF(ISNUMBER(MATCH({"ap65*","*two","three*","*four"},B1,0)),"low",IF(ISNUMBER(MATCH({"five*","*six","*seven","eight*"},B1,0)),"medium",IF(ISNUMBER(MATCH({"nine*","*ten","*eleven","twelve*"},B1,0)),"high","not found")))

[umang2382]

the wildcard works now, however the formula is not reading anything past the first criteria (ap65*)

[Pete_UK]

Attach a sample workbook, so that we might understand more clearly what you are trying to do. (The FAQ describes how to).

Pete

[umang2382]

test_criteria.xlsx

See attached. I'm trying to label column A (criticality) based on the contents of column B (also column U, but you can ignore this for now)

[Pete_UK]

Note that your cell references need adjusting in A2 - some parts of the formula refer to B2 and others to B1 (and even #REF!).

(This is not the problem, though).

Pete

[Pete_UK]

Try this formula in A2:

=IF(SUM(COUNTIF(B2,{"ap65*","*lo","CP-79*","HP*","*ilo","IOS*","lexmark*","*ctx*","NPI*","OA*","ZA*","APC*","American Power","netbotz"})),"low",IF(SUM(COUNTIF(B2,{"riverbed","imperva","*esx*"})),"medium",IF(SUM(COUNTIF(B2,{"airdefense","blue coat*"})),"high","not found")))

then copy down.

Hope this helps.

Pete

[umang2382]

that seems to work pretty well. And you help me better understand the use of the countif function thanks!

[Pete_UK]

Glad to hear it.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete