+ Reply to Thread
Results 1 to 14 of 14

Search a list for two key words in two columns and if true return value of a third column

  1. #1
    Registered User
    Join Date
    06-12-2014
    Posts
    19

    Search a list for two key words in two columns and if true return value of a third column

    Attached is my example workbook, some columns are left blank because that information is not important to this question. Essentially I want to create a formula/vba code that searches the Raw Data sheet. In this sheet I want to search Column G for the key word "missing" and at the same time I want to search column I for the keyword "paper". If both are found in the same row, I want to return the value of that row from column D. So in the attached workbook, I want the formula to find "missing", and "paper" and then output the ID Code value of 25 then also find and output the value of 75 because that also has "missing", and "paper" in its row. I want a list of all ID numbers that match these criteria to populate in the Analysis sheet of the workbook. Thanks for the help in advance!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor TheCman81's Avatar
    Join Date
    11-06-2012
    Location
    Edinburgh, UK
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    346

    Re: Search a list for two key words in two columns and if true return value of a third col

    You can use the following array formula entered with Ctrl Shift Ent

    You might want to think about replacing the hardcoded "missing"&"paper" with cell references

    =IFERROR(INDEX($D$2:$D$6,SMALL(IF(ISNUMBER(SEARCH("missing"&"paper",$G$2:$G$6&$I$2:$I$6)),ROW($G$2:$G$6)-ROW($G$2)+1),ROWS($A$1:A1))),"")
    Excel Guru in the making

    <----------If the question has been answered to your satisfication please click the Add Repuation star to the left

  3. #3
    Registered User
    Join Date
    06-12-2014
    Posts
    19

    Re: Search a list for two key words in two columns and if true return value of a third col

    Thanks for the quick response! Can you explain to me what the "-ROW($G$2)+1," part of the code does? I want to try and understand the formula so I can apply it to other things in the future

  4. #4
    Valued Forum Contributor TheCman81's Avatar
    Join Date
    11-06-2012
    Location
    Edinburgh, UK
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    346

    Re: Search a list for two key words in two columns and if true return value of a third col

    The ROW($G$2:$G$6)-ROW($G$2)+1 creates relative row positons to the TRUE values {1;2;3;4;5} and the results would look like this {1;FALSE;FALSE;FALSE;5}

    Basically gives the row position of each value that matches your criteria

  5. #5
    Registered User
    Join Date
    06-12-2014
    Posts
    19

    Re: Search a list for two key words in two columns and if true return value of a third col

    Is there a way to make this formula work for an open ended amount of rows, so instead of rows 2-6, can I modify the formula so that the number of rows goes 2-unknown #? I was thinking of just putting 2-100000 or something like that but is there a nicer way to write this into the formula?

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Search a list for two key words in two columns and if true return value of a third col

    Here's another one.

    Data Range
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    1
    ID Code
    ID Name
    ID
    Status
    Code
    Doc Group
    Missing
    Paper
    25
    2
    25
    missing
    paper
    75
    3
    26
    ok
    electronic
    4
    85
    approved
    paper
    5
    98
    missing
    electronic
    6
    75
    missing
    paper
    7
    ------
    ------
    ------
    ------
    ------
    ------
    ------
    ------
    ------
    ------


    This array formula** entered in M1:

    =IFERROR(INDEX(D:D,SMALL(IF(G$2:G$6=K$1,IF(I$2:I$6=L$1,ROW(D$2:D$6))),ROWS(M$1:M1))),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Copy down until you get blanks.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  7. #7
    Valued Forum Contributor TheCman81's Avatar
    Join Date
    11-06-2012
    Location
    Edinburgh, UK
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    346

    Re: Search a list for two key words in two columns and if true return value of a third col

    Will you expect your data range to reach 100K lines? by doing this you may slow down your spreadsheet

  8. #8
    Registered User
    Join Date
    06-12-2014
    Posts
    19

    Re: Search a list for two key words in two columns and if true return value of a third col

    I expect it to go up to maybe 1000 at most, so you suggest I just expand my range to 1000?

  9. #9
    Valued Forum Contributor TheCman81's Avatar
    Join Date
    11-06-2012
    Location
    Edinburgh, UK
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    346

    Re: Search a list for two key words in two columns and if true return value of a third col

    You can use something like this but please note, the use of INDIRECT must be used on the same workbook

    =IFERROR(INDEX(INDIRECT("D2:D"&COUNTA(D:D)),SMALL(IF(ISNUMBER(SEARCH("missing"&"paper",INDIRECT("G2:G"&COUNTA(D:D))&INDIRECT("I2:I"&COUNTA(D:D)))),ROW(INDIRECT("D2:D"&COUNTA(G:G)))-ROW($G$2)+1),ROWS($A$1:A1))),"")

  10. #10
    Registered User
    Join Date
    06-12-2014
    Posts
    19

    Re: Search a list for two key words in two columns and if true return value of a third col

    That worked thanks! This was a huge help!

  11. #11
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Search a list for two key words in two columns and if true return value of a third col

    Quote Originally Posted by TheCman81 View Post
    You can use something like this but please note, the use of INDIRECT must be used on the same workbook

    =IFERROR(INDEX(INDIRECT("D2:D"&COUNTA(D:D)),SMALL(IF(ISNUMBER(SEARCH("missing"&"paper",INDIRECT("G2:G"&COUNTA(D:D))&INDIRECT("I2:I"&COUNTA(D:D)))),ROW(INDIRECT("D2:D"&COUNTA(G:G)))-ROW($G$2)+1),ROWS($A$1:A1))),"")
    This would be much more efficient:

    =IFERROR(INDEX(D:D,SMALL(IF(G$2:G$1000=K$1,IF(I$2:I$1000=L$1,ROW(D$2:D$1000))),ROWS(M$1:M1))),"")

    Still array entered.

  12. #12
    Valued Forum Contributor TheCman81's Avatar
    Join Date
    11-06-2012
    Location
    Edinburgh, UK
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    346

    Re: Search a list for two key words in two columns and if true return value of a third col

    Quote Originally Posted by Tony Valko View Post
    This would be much more efficient:

    =IFERROR(INDEX(D:D,SMALL(IF(G$2:G$1000=K$1,IF(I$2:I$1000=L$1,ROW(D$2:D$1000))),ROWS(M$1:M1))),"")

    Still array entered.
    Yes this would be more efficent if your rows wouldn't go past 1000

  13. #13
    Valued Forum Contributor TheCman81's Avatar
    Join Date
    11-06-2012
    Location
    Edinburgh, UK
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    346

    Re: Search a list for two key words in two columns and if true return value of a third col

    Another way would be to set up a Pivot Table

    Example Pivot.xlsx

  14. #14
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Search a list for two key words in two columns and if true return value of a third col

    Quote Originally Posted by TheCman81 View Post
    You can use something like this but please note, the use of INDIRECT must be used on the same workbook

    =IFERROR(INDEX(INDIRECT("D2:D"&COUNTA(D:D)),SMALL(IF(ISNUMBER(SEARCH("missing"&"paper",INDIRECT("G2:G"&COUNTA(D:D))&INDIRECT("I2:I"&COUNTA(D:D)))),ROW(INDIRECT("D2:D"&COUNTA(G:G)))-ROW($G$2)+1),ROWS($A$1:A1))),"")
    Instead of using the volatile function INDIRECT define the ranges like this:

    For column D which is numeric data:

    =$D$2:INDEX($D:$D,MATCH(1E100,$D:$D))

    For columns G and I which are text data:

    =$G$2:INDEX($G:$G,MATCH("zzzzz",$G:$G))

    =$I$2:INDEX($I:$I,MATCH("zzzzz",$I:$I))

+ 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. Return True/False if a string of words is present in another list
    By tmahoney1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-05-2013, 05:39 PM
  2. Replies: 2
    Last Post: 02-17-2011, 10:15 PM
  3. Search column for value and return TRUE or FALSE
    By Bob Umlas in forum Excel Formulas & Functions
    Replies: 23
    Last Post: 07-13-2006, 12:10 PM
  4. [SOLVED] Search column for value and return TRUE or FALSE
    By Remote Desktop Connection hotkey in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  5. Search column for value and return TRUE or FALSE
    By Remote Desktop Connection hotkey in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1