+ Reply to Thread
Results 1 to 4 of 4

Return value from 1st Column based on Data found in corresponding Columns

  1. #1
    Registered User
    Join Date
    09-11-2013
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    2

    Return value from 1st Column based on Data found in corresponding Columns

    Hi, I've been using this forum to solve multiple challenging problems for some time , but I recently signed up to ask a question. Hopefully, I can get some assistance.

    Based on a date range that are a table column's names (column names are formatted as dates), I would like to return the values from the first column that are based on the values within the table.

    For example, based on the table below, for values that are 20% and less, between 11/1/2013 and 1/1/2014, I would like to return "Person A, Person C, Person D and Person F"

    TABLE

    ----------------------------------------------------
    | Resources| 09/13|10/13|11/13|12/13|01/14|02/14|
    ----------------------------------------------------
    |Person A | 0% | 30%|20%|10%|10%|20%|
    |Person B | 0% | 30%|20%|40%|40%|20%|
    |Person C | 0% | 30%|20%|10%|10%|10%|
    |Person D | 0% | 30%|20%|10%|10%|20%|
    |Person E | 0% | 30%|20%|40%|40%|20%|
    |Person F | 0% | 30%|20%|10%|10%|10%|
    ------------------------------------------
    RESULT

    ----------
    | Resources|
    ----------
    | Person A |
    | Person C |
    | Person D |
    | Person F |
    ----------

    Thank You!!!!
    Last edited by jahtrini; 09-11-2013 at 05:16 PM.

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

    Re: Return value from 1st Column based on Data found in corresponding Columns

    Can you use a helper column?


    Data Range
    A
    B
    C
    D
    E
    F
    G
    H
    1
    Resources
    9/13
    10/13
    11/13
    12/13
    1/14
    2/14
    T/F
    2
    Person A
    0%
    30%
    20%
    10%
    10%
    20%
    TRUE
    3
    Person B
    0%
    30%
    20%
    40%
    40%
    20%
    FALSE
    4
    Person C
    0%
    30%
    20%
    10%
    10%
    10%
    TRUE
    5
    Person D
    0%
    30%
    20%
    10%
    10%
    20%
    TRUE
    6
    Person E
    0%
    30%
    20%
    40%
    40%
    20%
    FALSE
    7
    Person F
    0%
    30%
    20%
    10%
    10%
    10%
    TRUE
    8
    9
    From
    To
    10
    Date Range
    1/14
    2/14
    11
    12
    Person A
    13
    Person C
    14
    Person D
    15
    Person F


    B1:G1 = 1st of the month dates formatted to display as m/yy

    B10:C10 = 1st of the month dates formatted to display as m/yy that define the date range

    Enter this array formula** in H2 and copy down as needed:

    =AND(INDEX(B2:G2,0,MATCH(B$10,B$1:G$1,0)):INDEX(B2:G2,0,MATCH(C$10,B$1:G$1,0))<=0.2)

    Enter this array formula** in A12:

    =IFERROR(INDEX(A$2:A$7,SMALL(IF(H$2:H$7,ROW(H$2:H$7)),ROWS(A$12:A12))-MIN(ROW(H$2:H$7))+1),"")

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

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    09-11-2013
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Return value from 1st Column based on Data found in corresponding Columns

    Wow! Thanks Tony This worked without a hitch

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

    Re: Return value from 1st Column based on Data found in corresponding Columns

    You're welcome. Thanks for the feedback!

+ 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: 10
    Last Post: 07-10-2012, 07:04 PM
  2. Comparing 2 columns- if a match is found return a value in 3rd column
    By lineson in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 12-20-2011, 04:48 PM
  3. give a value based on data found in a column - put need a value from it's row
    By hoolie in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-02-2009, 12:03 AM
  4. find name; search column; if date found-return 3 data pts; move to next row
    By cdl in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-28-2008, 10:43 AM
  5. If value found in data range (multiple columns) return row data
    By flickflick in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-23-2006, 05:46 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