+ Reply to Thread
Results 1 to 2 of 2

How search for text is in a column, if find then return text from next column over

  1. #1
    Registered User
    Join Date
    San Francisco
    MS-Off Ver
    Excel 2011

    Unhappy How search for text is in a column, if find then return text from next column over

    Our systems are set up so that they only have the store section, and a description of the part sold:
    Garden supplies - spraying can
    So it is missing a level that says whether the product is used for Watering, Pests, etc.

    Column A and B are where I put the some of the part name (enough to determine what the product is used for) and based on that piece of text the group name to which it belongs. This list would be ~100 rows long and keep growing.

    Column A – B
    1. Hose – Watering
    2. Spray – Watering
    3. Bug – Pests
    4. Mole – Pests
    Then I have Columns E and F right now, and am trying to figure out the formula to add G

    Column E – F - G
    1. Garden supplies - spraying can - Watering
    2. Garden supplies - green hose - Watering
    3. Garden supplies - long Hose by Hosers - Watering
    4. Garden supplies – bug repellent - Pests
    5. Garden supplies – mole traps – Pests
    6. Garden supplies – swing set – swing set

    The formula in G1 should look at column A1 (Hose), and looks to see if that text (doesn’t matter if capitalized or not) is in F1 (spraying can).
    Since it doesn’t find a match, then it looks to see if the text in A2 (Spray) is in F1 (spraying can). It is, so then it returns the text from on cell over in B2: “Watering”.
    It would do this through all the rows in A, and if it gets to the end and doesn’t find a match, then it would just return the product description (like swing set, above).

    I think I may need to use a VLOOKUP with a FIND or SEARCH, but not having any luck. Any advice is appreciated -

  2. #2
    Valued Forum Contributor khamilton's Avatar
    Join Date
    MS-Off Ver
    Excel 2007

    Re: How search for text is in a column, if find then return text from next column ove

    in G1 enter this formula =IF(ISERROR(LOOKUP(2^15,SEARCH($A$1:$A$4,F1),$B$1:$B$4)),F1,LOOKUP(2^15,SEARCH($A$1:$A$4,F1),$B$1:$B$4))
    Please acknowledge the response you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.

+ 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


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