+ Reply to Thread
Results 1 to 10 of 10

Return a value in a range based on two conditions in adjacent ranges

  1. #1
    Registered User
    Join Date
    12-23-2010
    Location
    Alamo, CA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Return a value in a range based on two conditions in adjacent ranges

    I'm trying return a value in a range from the row that matches two separate conditions.

    I tried to use this statement: =IF(AND(B2:B7="Tuesday",C2:C7="Shift 2",D2:D7,"")

    Looking to return the value 11 in D6

    A B C D
    Row 2 Monday Shift 1 8
    Row 3 Monday Shift 2 9
    Row 4 Monday Shift 3 7
    Row 5 Tuesday Shift 1 8
    Row 6 Tuesday Shift 2 11
    Row 7 Tuesday Shift 3 7


    Am I close???

  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 a value in a range based on two conditions in adjacent ranges

    One way...

    Use cells to hold the criteria:

    F2 = Tuesday
    G2 = Shift 2

    Then, this array formula** entered in H2:

    =INDEX(D2:D7,MATCH(G2,IF(B2:B7=F2,C2:C7),0))

    ** 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.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    12-23-2010
    Location
    Alamo, CA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Return a value in a range based on two conditions in adjacent ranges

    Here's a workbook with the example.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    12-23-2010
    Location
    Alamo, CA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Return a value in a range based on two conditions in adjacent ranges

    Is there an option without using the cells and only the range?

    I'm unable to ensure that the cells will always be populated with the same values.
    Last edited by riceguy; 03-13-2013 at 04:35 PM. Reason: clarification

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

    Re: Return a value in a range based on two conditions in adjacent ranges

    I don't understand what you mean?

    Do you mean you don't want to use cells F2:G2 to hold the lookup criteria?

  6. #6
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Return a value in a range based on two conditions in adjacent ranges

    Hi

    I think your code is over complicated hence your problem.

    paste this into E4 and copy it down.

    Please Login or Register  to view this content.
    Enjoy.

  7. #7
    Registered User
    Join Date
    12-23-2010
    Location
    Alamo, CA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Return a value in a range based on two conditions in adjacent ranges

    Tony:

    Correct.

    I am looking to search a defined range for each of these values (Day and shift) and return a value when there is a match.

    The reason I'm trying not to define the cells (F2,G2) is that I can't depend on the data that will populate the date and shift range being consistent.

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

    Re: Return a value in a range based on two conditions in adjacent ranges

    Ok, just replace the cell references of the lookup values with the hardcoded strings:

    =INDEX(D2:D7,MATCH("Shift 2",IF(B2:B7="Tuesday",C2:C7),0))

    Still array entered.

  9. #9
    Registered User
    Join Date
    12-23-2010
    Location
    Alamo, CA
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: Return a value in a range based on two conditions in adjacent ranges

    Now we're talkin'.

    Thanks!
    Last edited by riceguy; 03-13-2013 at 06:42 PM. Reason: spelling correction

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

    Re: Return a value in a range based on two conditions in adjacent ranges

    You're welcome!

+ 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

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