+ Reply to Thread
Results 1 to 6 of 6

Expand 'Search' to 2 columns

  1. #1
    Forum Contributor
    Join Date
    05-10-2011
    Location
    Central Ohio
    MS-Off Ver
    Excel 2000, and 2010
    Posts
    654

    Expand 'Search' to 2 columns

    The formula liste below works but I would like to expand the search boundaries to include column 'AC' as well as 'AD'. Something on the line of 'Search AC3 or AD3' but I am not sure how to include this in the formula.

    Please Login or Register  to view this content.
    Thanks
    Jim O
    Last edited by JO505; 11-22-2015 at 06:30 PM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Expand 'Search' to 2 columns

    Try this:

    =IF(OR(ISNUMBER(SEARCH($A$14,AC3)),ISNUMBER(SEARCH($A$14,AD3))),MAX($I$2:$I2)+1,0)

    Hope this helps.

    Pete

  3. #3
    Forum Contributor
    Join Date
    05-10-2011
    Location
    Central Ohio
    MS-Off Ver
    Excel 2000, and 2010
    Posts
    654

    Re: Expand 'Search' to 2 columns

    Pete,

    Thanks for the quick response. That appears to be just the ticket. Thank much. One further question if I might, out side of a formula getting too large and bulky is their any limit to the number of "ORs' that can be added?

    Again Thanks and I will consider this as solved.

    Jim O

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Expand 'Search' to 2 columns

    In Excel 2003 and earlier the limit was 31 arguments, but in XL2007 and later that is now 255.

    Hope this helps.

    Pete

  5. #5
    Forum Contributor
    Join Date
    05-10-2011
    Location
    Central Ohio
    MS-Off Ver
    Excel 2000, and 2010
    Posts
    654

    Re: Expand 'Search' to 2 columns

    255 arguments! I would hate to see that formula! I was just curious.

    Thanks
    Jim O

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Expand 'Search' to 2 columns

    Thanks for the rep. You could also do it like this:

    =IF(COUNTIF(AC3:AD3,"*"&$A$14&"*")>0,MAX($I$2:$I2)+1,0)

    Hope this helps.

    Pete

+ 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. Expand selection and search
    By Excel0ne in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-10-2014, 10:26 AM
  2. Can i make the columns expand?
    By youngsequan in forum Excel General
    Replies: 0
    Last Post: 12-04-2009, 11:38 AM
  3. Columns - Expand and Collapse
    By Cecil in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-04-2008, 10:54 AM
  4. [SOLVED] Minimize or Expand Columns
    By dwillman in forum Excel General
    Replies: 2
    Last Post: 08-11-2006, 05:31 PM
  5. [SOLVED] Expand and Collapse Columns
    By JVICO in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 04-20-2006, 09:15 PM
  6. [SOLVED] How do I expand Excel lookup function to search more than 16,384 r
    By Rusty in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-29-2006, 05:35 PM
  7. how can i expand the max number of columns?
    By brya6347 in forum Excel General
    Replies: 0
    Last Post: 02-22-2005, 12:50 PM

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