+ Reply to Thread
Results 1 to 11 of 11

Search for multiple Criteria in an array

  1. #1
    Registered User
    Join Date
    04-07-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    3

    Search for multiple Criteria in an array

    Hello,

    I am attempting to use an IF function to return the value "Government" when any of the four cells I'm looking in contain a variety of names that are considered government for my process.

    I have about 10 names that could all be condsidered government listed on a separate tab.
    Ex: A1-A10 would be "DFAS", "VA", "Army", "Navy", "Military" , etc.

    The cells I'm looking in have long names like "Southwestern Military Hospital" or "Shorewood Partners for VA Medical Center". However, I only need to see the word "military" or "VA" show up at all in any of the four cells Im searching through.

    What I have been doing is concatenating the four cells into one and using a wilcard in the criteria of my ISNUMBER SEARCH function to look in the concatenated cell. When Im dealing with parts that dont have multiple names my formula looks like this:

    =IF(ISNUMBER(SEARCH("*Alliance*", F1)), "Alliance", "x") where F1 is the concatenated results of the four cells I'm looking for the word "Alliance" in.

    I've tried things like =IF(ISNUMBER(SEARCH(OR("DFAS","VA","Army"),F1)),"Government","x") where I reference the cells where DFAS, VA, and Army are located (rather than writing them out as shown above) on a serparate sheet, but I'm having no luck.

    Could I use an array or some type of vlookup for the multiple search criteria? I'm trying to avoid writing a string of 10 IF statements for each verison of Government names I'm looking for.

    Thanks!
    Justin

  2. #2
    Valued Forum Contributor
    Join Date
    02-09-2006
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 2016
    Posts
    1,075

    Re: Search for multiple Criteria in an array

    Could you attach a copy of your workbook? It's much easier to help you. Remember to remove/replace any sensitive data before uploading.
    Trish in Oz
    -------------
    A problem well defined is a puzzle half solved


    If you attach a sample of your workbook it will be easier to find a solution. Remember to remove/replace sensitive data before uploading the file. Look here if you need help with attachments:
    http://www.excelforum.com/faq.php?fa...b3_attachments

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Search for multiple Criteria in an array

    Try something like this...

    Data Range
    A
    B
    1
    DFAS
    2
    VA
    3
    Army
    4
    Navy
    5
    Military
    6
    7
    8
    9
    10
    Southwestern Military Hospital
    Government
    11
    try this one
    12
    Shorewood Partners for VA Medical Center
    Government


    This formula entered in B10 and copied down:

    =IF(ISNUMBER(LOOKUP(35000,SEARCH(A$1:A$5,A10))),"Government","")
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    04-07-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Search for multiple Criteria in an array

    It worked! This is great thank you so much!
    Would you mind explaining the logic behind the lookup embeded within the isnumber along with the use of 35,000?
    Thanks!

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Search for multiple Criteria in an array

    Here goes...

    =IF(ISNUMBER(LOOKUP(35000,SEARCH(A$1:A$5,A10))),"Government","")

    The SEARCH function is looking for each of the keywords in A1:A5 within the string in cell A10.

    If a keyword is found SEARCH returns the starting character number of the keyword. If a keyword is not found SEARCH returns the #VALUE! error. For example:

    A10 = Southwestern Military Hospital

    SEARCH("DFAS",A10) = #VALUE!
    SEARCH("VA",A10) = #VALUE!
    SEARCH("ARMY",A10) = #VALUE!
    SEARCH("Navy",A10) = #VALUE!
    SEARCH("Military",A10) = 14

    The keyword "Military" was found within the string starting at character number 14.

    This array of results are then passed to the LOOKUP function:

    LOOKUP(35000,{#VALUE!;#VALUE!;#VALUE!;#VALUE!;14})

    A cell can contain a maximum of 32,767 characters. So, the largest number that SEARCH can possibly return is 32,767.

    The way that LOOKUP works in this application is if the lookup value 35000 is greater than any number value in the array {#VALUE!;#VALUE!;#VALUE!;#VALUE!;14}, then it returns the LAST number in the array that is less than the lookup value. In this case there is just a single number in the array {#VALUE!;#VALUE!;#VALUE!;#VALUE!;14} and it is less than 35000 so the result of the LOOKUP function is 14.

    LOOKUP(35000,{#VALUE!;#VALUE!;#VALUE!;#VALUE!;14}) = 14

    I use a lookup value of 35000 because it is guaranteed to be greater than the max number that SEARCH can return, 32,767. This guarantees that the LOOKUP function will return the correct expected result.

    If a string might contain more than one keyword:

    A10 = Southwestern VA Military Hospital

    Then:

    SEARCH("DFAS",A10) = #VALUE!
    SEARCH("VA",A10) = 14
    SEARCH("ARMY",A10) = #VALUE!
    SEARCH("Navy",A10) = #VALUE!
    SEARCH("Military",A10) = 17

    The array passed to the LOOKUP function is:

    {#VALUE!;14;#VALUE!;#VALUE!;17}

    The last number that is less than the lookup value is 17, So:

    LOOKUP(35000,{#VALUE!;14;#VALUE!;#VALUE!;17}) = 17

    If no keywords are found within the string then the SEARCH array will contain only #VALUE! errors.

    A11 = try this one

    SEARCH("DFAS",A11) = #VALUE!
    SEARCH("VA",A11) = #VALUE!
    SEARCH("ARMY",A11) = #VALUE!
    SEARCH("Navy",A11) = #VALUE!
    SEARCH("Military",A11) = #VALUE!

    In this case the LOOKUP function will return the #N/A error:

    LOOKUP(35000,{#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}) = #N/A

    The result of the LOOKUP function is then passed to the ISNUMBER function:

    ISNUMBER(14) = TRUE
    ISNUMBER(#N/A) = FALSE

    When ISNUMBER is TRUE the formula returns the value_if_true argument of the IF function:

    IF(ISNUMBER(14),"Government","") = Government

    When ISNUMBER is FALSE the formula returns the value_if_false argument of the IF function:

    IF(ISNUMBER(#N/A),"Government","") = "" (a blank)


    Hope that all makes sense!

  6. #6
    Registered User
    Join Date
    04-07-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Search for multiple Criteria in an array

    This is absolutely genius, makes perfect sense. You are the man. Thank you so much!

  7. #7
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Search for multiple Criteria in an array

    You're welcome. Thanks for the feedback!


    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

  8. #8
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Search for multiple Criteria in an array

    Quote Originally Posted by Tony Valko View Post
    I use a lookup value of 35000 because it is guaranteed to be greater than the max number that SEARCH can return, 32,767.
    Although 2^15 would've saved a character
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  9. #9
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Search for multiple Criteria in an array

    Quote Originally Posted by XOR LX View Post
    Although 2^15 would've saved a character
    Yeah, but 2^15 has to be calculated while 35000 is a constant!

  10. #10
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Search for multiple Criteria in an array

    Ok, you win!

  11. #11
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Search for multiple Criteria in an array

    I'll meet you in the middle...

    If we want to save a couple of keystrokes we can replace ISNUMBER with COUNT.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Multiple Criteria search and COUNT FOR TEXT across multiple sheet ARRAY's
    By akaushik25 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-25-2014, 08:58 AM
  2. [SOLVED] Multiple Criteria Isnumber Search Array (with Attachment Example)
    By sweetrevelation in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-13-2013, 05:45 AM
  3. Replies: 8
    Last Post: 08-22-2013, 07:00 AM
  4. Search an Array for a Value with Multiple Criteria
    By NDev in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-04-2013, 07:02 PM
  5. [SOLVED] VBA Array w/multiple search criteria
    By Jeepster325 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-26-2013, 06:07 PM

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