+ Reply to Thread
Results 1 to 8 of 8

Multiple Criteria Isnumber Search Array (with Attachment Example)

  1. #1
    Registered User
    Join Date
    04-21-2009
    Location
    Puyallup, WA
    MS-Off Ver
    Excel 2010
    Posts
    55

    Multiple Criteria Isnumber Search Array (with Attachment Example)

    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?

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

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

    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.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    04-21-2009
    Location
    Puyallup, WA
    MS-Off Ver
    Excel 2010
    Posts
    55

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

    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).

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

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

    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

  5. #5
    Registered User
    Join Date
    04-21-2009
    Location
    Puyallup, WA
    MS-Off Ver
    Excel 2010
    Posts
    55

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

    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.
    Last edited by sweetrevelation; 08-30-2012 at 02:52 PM.

  6. #6
    Registered User
    Join Date
    04-21-2009
    Location
    Puyallup, WA
    MS-Off Ver
    Excel 2010
    Posts
    55

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

    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!

  7. #7
    Registered User
    Join Date
    11-13-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    6

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

    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?

  8. #8
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

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

    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.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ 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

Bookmarks

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