+ Reply to Thread
Results 1 to 12 of 12

Search a column for up to 30 words then insert a different word in a separate column

  1. #1
    Registered User
    Join Date
    12-18-2014
    Location
    Seattle
    MS-Off Ver
    2013
    Posts
    6

    Search a column for up to 30 words then insert a different word in a separate column

    Hello, first post. I am trying to create what I think is an IF formula. In column C, I want to search the entire column for a word and place another word in column D in the same row. Then I want to be able to search column D for a word and in the same row in insert a different word in column E. For example, if the word "school" is found in a row in column C, I need to place "Bus" in column D in the same row as "school" was in column C. Then if "Bus" is found in column D, I need "45" to appear in column E in the same row as "School" and "Bus".

    Is this doable? Seems pretty straight forward but not sure how to accomplish this. I need to enter approx 30 words to search for in column C and D and input approx 30 different numbers in column E. Thank you so much!

  2. #2
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    Re: Search a column for up to 30 words then insert a different word in a separate column

    Do you have any sample data already input into a workbook that we could look at to start with?
    Spread the love, add to the Rep

    "None of us are as smart as all of us."

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,579

    Re: Search a column for up to 30 words then insert a different word in a separate column

    Sounds like you will need to work with INDEX/MATCH or VLOOKUP Tables. An example sheet (Go Advanced>Manage Attachments) will help.
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Registered User
    Join Date
    12-18-2014
    Location
    Seattle
    MS-Off Ver
    2013
    Posts
    6

    Re: Search a column for up to 30 words then insert a different word in a separate column

    Screenshot (1).png

    Here is a screen shot

  5. #5
    Registered User
    Join Date
    12-18-2014
    Location
    Seattle
    MS-Off Ver
    2013
    Posts
    6

    Re: Search a column for up to 30 words then insert a different word in a separate column

    Quote Originally Posted by ChemistB View Post
    Sounds like you will need to work with INDEX/MATCH or VLOOKUP Tables. An example sheet (Go Advanced>Manage Attachments) will help.
    Can you elaborate? Not sure what you meant by " An example sheet (Go Advanced>Manage Attachments) will help"? Are these types of tables that I incorporate into my sheet?

  6. #6
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    Re: Search a column for up to 30 words then insert a different word in a separate column

    The Go Advanced>Manage Attachments is how you navigate the forum tools to insert a sample workbook. Although your screenshot is informative, it is harder to work with than a sample workbook containing sample data.

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,579

    Re: Search a column for up to 30 words then insert a different word in a separate column

    and for whatever reason, I cannot see png images so the image helps me not at all.

  8. #8
    Registered User
    Join Date
    12-18-2014
    Location
    Seattle
    MS-Off Ver
    2013
    Posts
    6

    Re: Search a column for up to 30 words then insert a different word in a separate column

    TestBook.xlsx

    Like this:??

    Basically I have a list of chemicals in column C and I'd like it to perform so it automatically inserts the chemical name and EPA reg number in the following columns ( D & E ) and same row. I know I have to link them all together, just clueless on how to perform this.
    Last edited by SFore; 12-18-2014 at 02:30 PM.

  9. #9
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,579

    Re: Search a column for up to 30 words then insert a different word in a separate column

    Okay, look at this example.
    I added Sheet2 which has Table1 with Name/Chemical/EPA Number

    On Sheet1, D1, I put this formula. It looks for the value in C1 in Table 1 Column A. If it finds it, it returns the value in the same row in Col B. Copy that formula all the way down the data page.

    =IFERROR(VLOOKUP(C1,Table1,2,FALSE),"")

    In E1 is a similar formula but returning the value from the 3rd column (C)

    =IFERROR(VLOOKUP(C1,Table1,3,FALSE),"")
    Are you looking for something like that to work with?
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    12-18-2014
    Location
    Seattle
    MS-Off Ver
    2013
    Posts
    6

    Re: Search a column for up to 30 words then insert a different word in a separate column

    Wow, thank you so much! Now in terms of copying the formula....it seems it's not as simple as a copy paste correct? Do i have to paste it line by line to increase the row value?

  11. #11
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,579

    Re: Search a column for up to 30 words then insert a different word in a separate column

    No, you can copy>paste the entire range or even drag it by the lower right hand corner.

  12. #12
    Registered User
    Join Date
    12-18-2014
    Location
    Seattle
    MS-Off Ver
    2013
    Posts
    6

    Re: Search a column for up to 30 words then insert a different word in a separate column

    Thank you so much ChemistB, I was having problems pasting it because I continued my column further than my column C value. Much appreciated!

+ 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. Replies: 3
    Last Post: 07-15-2014, 03:42 PM
  2. macro if data in column a = set word to insert that column into sheet 2 or 3 based on word
    By ExcelBeginner326 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-13-2014, 01:57 PM
  3. [SOLVED] How to search for text within a column and return a value in a separate column
    By hulayogi in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-19-2013, 10:34 PM
  4. Replies: 2
    Last Post: 04-29-2011, 10:53 AM
  5. Insert word prior to column of words
    By Owen19102003 in forum Excel General
    Replies: 2
    Last Post: 06-24-2008, 01:41 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