+ Reply to Thread
Results 1 to 12 of 12

excel formula -search a range

  1. #1
    Registered User
    Join Date
    09-10-2012
    Location
    texas usa
    MS-Off Ver
    Excel 2010
    Posts
    50

    excel formula -search a range

    I have attached a sheet
    used formula =IFERROR(LOOKUP(1,-SEARCH($K$11:$K$13,$C5),"TREK "&C5),C5)
    works on first line but not when copy down
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,267

    Re: excel formula -search a range

    Does the following in H5 entered as an array formula with ctrl-shift-enter then copied down to H8 do what you want?

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Geoff

    Did I help significantly? If you wish, click on * Add Reputation to say thanks.
    If your problem has been resolved please select “Solved” from the Thread Tools menu

  3. #3
    Registered User
    Join Date
    09-10-2012
    Location
    texas usa
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: excel formula -search a range

    Geoff
    Thanks the formula works great. However I have one variation
    If the description already contains" "trek" trek is added again.
    I have tried to nest conditions on your formula so it would not
    add trek if already there but can't
    seem to get the right combination. Also your formula works
    ok without doing ctrl-shift-enter
    Thanks Carroll

  4. #4
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,267

    Re: excel formula -search a range

    Try the following in H5:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    For this to work when copied down I believe it does need to be entered as an array formula (the SEARCH($K$11:$K$13 bit of the formula is what causes the need. Certainly if you don't enter as an array formula the results are different (and I think wrong) for rows 7 & 8.

    Copy H5 down to H8

    Let me know whether this works for you.

  5. #5
    Registered User
    Join Date
    09-10-2012
    Location
    texas usa
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: excel formula -search a range

    Geoff
    Thanks the formula works great. However I have one variation
    If the description already contains" "trek" trek is added again.
    I have tried to nest conditions on your formula so it would not
    add trek if already there but can't
    seem to get the right combination. Also your formula works
    ok without doing ctrl-shift-enter
    Thanks Carroll

    I have attached a new sheet where I tried formula =IF(ISNUMBER(SEARCH("TREK",B2)),B2,IF(SUM(NOT(ISERROR(SEARCH($J$2:$J$5, B2)))+0)>0, "TREK " & B2, B2))
    see new sheet attached it worked and did not work
    Thanks
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    09-10-2012
    Location
    texas usa
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: excel formula -search a range

    Geoff
    Sorry, I think I sent the wrong sheet.
    correct one is attached. I did use the ctrl-shift-enter to save and then copied down. Still get
    the two instances where it does not work. Your formula works great except for when trek already occurs.
    the new sheet will show this the brackets that show when ctrl-shift-enter is used also show

  7. #7
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,267

    Re: excel formula -search a range

    Quote Originally Posted by carrollm View Post
    Geoff
    However I have one variation
    If the description already contains" "trek" trek is added again.
    I have tried to nest conditions on your formula so it would not
    add trek if already there but can't
    seem to get the right combination.
    I'm not clear on what you are looking for in K4:K5. The above quote led me to believe that both your formula in post #4 and mine in post #5 were giving correct results.

    What are your expected results for K4:K5 ?

    Is it that you want to suppress adding "TREK" ONLY IF the description already STARTS with "TREK" ?
    If so then try the following in K2 entered as an array formula then copied down to K8:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    09-10-2012
    Location
    texas usa
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: excel formula -search a range

    j4:j6 are names that should have trek at the start of the name.
    The formula k2, k3 and k7correctly added trek but did not in k4 and k5
    Since "used" is not in j4:j6 then the formula did not add trek to k6
    since b8 already has trek in the description, trek was not added in k8..
    Hope that helps
    so bottom line is, I don't understand why trek was not added in k4 and k5 since
    checkpoint and madone is in j4:j6 and trek is not already in b4 and b5

    Carroll

  9. #9
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,267

    Re: excel formula -search a range

    1) You are talking about j4:j6. Has the spreadsheet moved on? Relative to your post #6 attachment I think you mean j2:j5.

    2) Did you try my post #7 formula in your post #6 attachment? It gives the following results:

    madone1_gw_revB.png

    Isn't this what you are expecting?

  10. #10
    Registered User
    Join Date
    09-10-2012
    Location
    texas usa
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: excel formula -search a range

    You are right Geoff - formula in your post 7. my misunderstanding.
    Thanks much

  11. #11
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    6,455

    Re: excel formula -search a range

    Another non-array formula:

    Please Login or Register  to view this content.
    Just Enter

  12. #12
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,267

    Re: excel formula -search a range

    Quote Originally Posted by carrollm View Post
    You are right Geoff - formula in your post 7. my misunderstanding.
    Thanks much
    bebo021999's formula is a different approach but gets the same results. Now you have two choices! Thanks for the rep

+ 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. [SOLVED] Formula to search range, without If / Then / Else
    By snuffnchess in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-27-2018, 07:56 PM
  2. Replies: 2
    Last Post: 02-16-2016, 03:23 PM
  3. Use array formula for range using search
    By hiker100 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-19-2015, 09:41 AM
  4. [SOLVED] formula to search a range
    By Rob (SA) in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-15-2014, 02:10 PM
  5. Modify this Search Formula to search across a range of cells
    By zicitron in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-16-2013, 08:11 AM
  6. Formula To Search IP Range for Location
    By atnextc in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-03-2012, 10:36 AM
  7. Formula to search if a value is in range of 2 columns
    By tvanhooz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-21-2011, 01:15 AM

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