Multiple Criteria Isnumber Search Array (with Attachment Example)

**sweetrevelation** · 08-30-2012, 08:57 AM

Is there an easier way to do what I'm trying to achieve?

I am currently using an isnumber search formula to search for strings. Problem is, I ran out of room in the formula box.

Attached is an example
IsNumber_Search_Multiple_Criteria.xlsx

Example Code

Please Login or Register  to view this content.

I'd like to be able to use some kind of easier formula to work with. One where I can type it in an array-type formula like:

Please Login or Register  to view this content.

Is there any way to do a multiple criteria isnumber search array?

**NBVC** · 08-30-2012, 09:07 AM

In a separate area of the sheet, list all the words to find and in adjacent column what you want returned if found.

Then apply this formula"

=IFERROR(LOOKUP(10^10,SEARCH($D$2:$D$5,A2),$E$2:$E$5),FALSE)

where D2:D5 contains the keywords, and E2:E5 contain the corresponding words to insert....

adjust ranges to suit. This is still a regular (non-array) formula.

**sweetrevelation** · 08-30-2012, 10:55 AM

Thanks NBVC. This formula works but it's not exactly user-friendly. I'm not ungrateful for your solution though!

Your formula requires me to have to manually update the formula range. This is an issue because I will have thousands of text strings and this formula is an attempt to categorize/classify/label them. Having to put the label column over and over again is also redundant. I'd like to have a header and let the result vector part of that formula be the result.

For Example, imagine to the left, I have my keyword list in column A. In Column B will be where the formula goes. A blank space in Column C & D. Starting in Column F, I have several columns side by side in the same worksheet with headers : Brand, Competitors, Material, Price, Type

Under those column headers, I could type on-the-fly (categories) and have the keyword list become labeled using SOME formula similar to the one you've created (without having to update ranges or redundantly type the label each time).

**NBVC** · 08-30-2012, 12:08 PM

Ok, I think I understand...

So lets assume you have your phrases in column A, and that you have the categories listed in F1:G1 and your corresponding keywords below those header, covering F2:G4 (may contain blanks).

Then in B2, try:

=IFERROR(INDEX($E$1:$F$1,MIN(IF(ISNUMBER(SEARCH(IF($E$2:$F$4="","||",$E$2:$F$4),A2)),COLUMN($E$1:$F$1)-COLUMN($E$1)+1,COUNTA($E$1:$F$1)+1))),"")

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down

**sweetrevelation** · 08-30-2012, 02:38 PM

That is the most complex but AWESOMEST formula ever! That's exactly what I was trying to achieve. Thanks for taking the time to help me NBVC since you didn't have to. Very kind.

Question: Since this formula is beyond my Excel skills, if I wanted to add a new column to the right, what parts of the formula would I have to change? By "add a new column" I mean, in the example, we have "Training" as a column, "Formulas" as a column. If I wanted to add say "Tutorials" in the next column, how would I do that by changing the formula?

I'm going to try to obviously figure it out on my own but any help would help.

**sweetrevelation** · 08-30-2012, 03:17 PM

Premature of me. I figured out how to modify the formula for additional columns.

In case someone else comes across this thread, here's how:

Please Login or Register  to view this content.

In the formula above, you can change the 135 part to whatever range you'd like:

Please Login or Register  to view this content.

You will also need to change $O$1 part to the range of your headings:

Please Login or Register  to view this content.

Thanks again NBVC!

**Mikeburbs** · 11-13-2013, 05:13 AM

Hi - I think i'm trying to do something similar (but not sure) - I have a bank statement which has come from online banking and i'm trying to categorize everything. For example i have fuel from a number of different suppliers on my statement but i want the category to be fuel. So i have an if statement to look up the string but this has to be done in seperate columns, i want it to search based on a table of a number of different possible strings and apply a category. Can anyone help?

**arlu1201** · 11-13-2013, 05:45 AM

Mikeburbs,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

Multiple Criteria Isnumber Search Array (with Attachment Example)

LinkBack

Thread Tools

Rate This Thread

Display

Multiple Criteria Isnumber Search Array (with Attachment Example)

Re: Multiple Criteria Isnumber Search Array (with Attachment Example)

Re: Multiple Criteria Isnumber Search Array (with Attachment Example)

Re: Multiple Criteria Isnumber Search Array (with Attachment Example)

Re: Multiple Criteria Isnumber Search Array (with Attachment Example)

Re: Multiple Criteria Isnumber Search Array (with Attachment Example)

Re: Multiple Criteria Isnumber Search Array (with Attachment Example)

Re: Multiple Criteria Isnumber Search Array (with Attachment Example)

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions