+ Reply to Thread
Results 1 to 12 of 12

Look for a list of keywords in a cell and then return the content of the found cell

  1. #1
    Registered User
    Join Date
    10-09-2013
    Location
    Randers, Denmark
    MS-Off Ver
    Excel 2010
    Posts
    6

    Look for a list of keywords in a cell and then return the content of the found cell

    Hi all users

    I have for several times been searching the web for answers on certain Excel challenges that I've had.
    I have come across this forum a few times, and now it was the time to sign up for it :-)

    And I have a challenge which I hope that you can help me with.
    I have a long list (approx. 1500) of cells which consist of text strings.
    This would be an example on 7 of the cells:

    D.Eng C LC2A-NL30 11/10-F-12-1760
    D.Eng C LC2A-NL30 11/10-F-12-1800
    D.Eng C LC2A-NL30 13/12-F-12-2000
    D.Eng C LC2A-NL30 14/13-F-12-2100
    D.Eng C LC2A-NL30 15/14-F-12-2200
    D.Eng C LC2A-NL30 15/14-F-12-2350
    D.Eng C LC2A-NL30 16/15-F-12-2400

    What I need here is a way to search for certain keywords within the list of cells, and if I have a match for all of the keyword, I would like to return the content of the found cell.
    I think another example would be good.

    Keyword 1: NL
    Keyword 2: 2000
    Keyword 3: 10

    I need to find a cell where keyword 1 and keyword 2 is included.
    Furthermore I need the value after the / to be equal or higher than keyword 3.
    In this case there is a cell that lives up to all of this (number 3 from the top) and then I would like to return the content of this cell.

    In my world this isn't really that simple, so I really hope that you guys can help me.

    Thanks in advance

    - CLARK

  2. #2
    Forum Contributor
    Join Date
    10-01-2013
    Location
    Cameron, Mo
    MS-Off Ver
    Excel 2010
    Posts
    113

    Re: Look for a list of keywords in a cell and then return the content of the found cell

    This is what I came up with for your examples. You can change your key words at the top to check different results. I hope this gets you close to what you need. Good luck.

    This formula is copied from cell b2 of my worksheet.

    =IF(IFERROR(FIND($C$1,A2),0)>0,IF(IFERROR(FIND($E$1,A2),0)>0,IF(IFERROR(INT(MID(A3,FIND("/",A3)+1,2)),0)>$G$1,A2,""),""))
    Attached Files Attached Files

  3. #3
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,793

    Re: Look for a list of keywords in a cell and then return the content of the found cell

    Keyword:3 10 represents which part of the text

  4. #4
    Registered User
    Join Date
    10-09-2013
    Location
    Randers, Denmark
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Look for a list of keywords in a cell and then return the content of the found cell

    Hi Motu040602

    Thanks for the fast answer.
    Quite a formula you came up with there.

    I'm sitting back with a few questions now.
    I need to type this formula into only one cell and then look into an area of cells.
    So I think we need to add a range of cells into the formula instead of a specific reference to eg. A2.
    And this is where I saw the complexity because how are you returning the value of a cell for which you don't know the number on beforehand.
    E.g. it came out to be that A123 was containing the keywords I was looking for, then it should be return the content of cell A123. But I don't know that this was the cell I was lookinon beforehand.

    What do you think based on this..?

    Thanks

  5. #5
    Registered User
    Join Date
    10-09-2013
    Location
    Randers, Denmark
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Look for a list of keywords in a cell and then return the content of the found cell

    Hi nflsales

    Keyword 3 should represent the value after the "/".
    D.Eng C LC2A-NL30 11/10-F-12-1760

  6. #6
    Forum Contributor
    Join Date
    10-01-2013
    Location
    Cameron, Mo
    MS-Off Ver
    Excel 2010
    Posts
    113

    Re: Look for a list of keywords in a cell and then return the content of the found cell

    Will every search only yield one answer? I can use an array formula to search out a large range of cells and return the value back, but it is a bit more involved. and will only find the 1st answer it comes to that matches all 3 criteria. I can't work on it right now, but I will give it shot in a couple of hours. Or maybe someone else can pick up sooner. I will check back in little while. Thanks

  7. #7
    Registered User
    Join Date
    10-09-2013
    Location
    Randers, Denmark
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Look for a list of keywords in a cell and then return the content of the found cell

    Hi Moto040602

    Yes the search should only yield one answer and it's fine that it only will find the 1st answer that matches all 3 criteria. That is exactly what I need here!
    There could be several cells that would match the first two criteria, but I would like to find the first one, which has a value higher than criteria 3. This seems to fits excellent to what you describe.

    NB: The criteria 3 could be both two digits and three digits. Just to mention it.
    D.Eng C JU4H-NL54 103/94-F-12-2400
    D.Eng C JU4H-NL54 114/104-F-12-2600

    Thanks for looking into this. I fully appreciate it!!

  8. #8
    Forum Contributor
    Join Date
    10-01-2013
    Location
    Cameron, Mo
    MS-Off Ver
    Excel 2010
    Posts
    113

    Re: Look for a list of keywords in a cell and then return the content of the found cell

    I think this is what you need. The formula is HUGE. Sorry

    =IFERROR(INDEX(A2:A8,MATCH(1,(IF(IFERROR(FIND(C1,A2:A8),0)>0,1,0))*(IF(IFERROR(FIND(E1,A2:A8),0)>0,1,0)*(IF(INT(MID(A2:A8,FIND("/",A2:A8)+1,IF(MID(A2:A8,FIND("/",A2:A8)+3,1)="-",2,3)))>G1,1,0))),0),1),"")


    Load your Keywords at the top like before and in the yellow box you should see your result with red letter. Thanks for the challenge! I hope I got it.
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    10-01-2013
    Location
    Cameron, Mo
    MS-Off Ver
    Excel 2010
    Posts
    113

    Re: Look for a list of keywords in a cell and then return the content of the found cell

    One more thing this is an array formula so you may need go into the formula cell and while holding down ctrl + shift hit enter to activate the array formula.

  10. #10
    Registered User
    Join Date
    10-09-2013
    Location
    Randers, Denmark
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Look for a list of keywords in a cell and then return the content of the found cell

    Hi Motu040602

    Yes that is quite a big formula, but guess what.... IT WORKS :D
    It does exactly what I need it to do, so I can confirm that you got it.

    As you already saw coming, I ran into problems with the array formula, but with your small trick I managed to get it working.

    Thank you a lot for the help. It was great!!

  11. #11
    Forum Contributor
    Join Date
    10-01-2013
    Location
    Cameron, Mo
    MS-Off Ver
    Excel 2010
    Posts
    113

    Re: Look for a list of keywords in a cell and then return the content of the found cell

    Excellent! If it isn't to much trouble help me build my rep by hitting the star to the lower left. Thanks!

  12. #12
    Registered User
    Join Date
    10-09-2013
    Location
    Randers, Denmark
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Look for a list of keywords in a cell and then return the content of the found cell

    Consider it done ;-)

+ 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] Search content from cell A1 in Sheet2 and select the content found
    By excelcandy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-04-2013, 06:45 PM
  2. IF same CONTENT FOUND IN CELL A THEN DELETE CONTENT IN B,C or D
    By mecutemecute in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-11-2013, 01:16 AM
  3. Change cell content if text is found in different cell
    By obydesign in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-01-2011, 07:20 AM
  4. Replies: 4
    Last Post: 05-12-2011, 02:25 PM
  5. Replies: 2
    Last Post: 11-24-2010, 05:44 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