+ Reply to Thread
Results 1 to 12 of 12

Return Text if True based on multiple criteria

  1. #1
    Registered User
    Join Date
    06-10-2013
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    13

    Return Text if True based on multiple criteria

    I am using Excel 2007/2010.

    I would like to know if there is a formula that would return text values if TRUE based on given criteria.

    On sheet 1, I have a tracker that tracks several bits of data.

    On sheet 2, I would like to analyze the data contained on sheet 1 based on certain criteria.

    I have attached a simple sample excel file of what I am looking for.

    Sheet 1 has 3 columns
    A: Date
    B: Region
    C: Name

    Sheet 2 has 3 fields of a form
    C1: Region (entered manually)
    C2: Date (entered manually)
    C4: Name (return name if date = C2 and region = c1)

    Thanks in advance for your help!
    Attached Files Attached Files

  2. #2
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Return Text if True based on multiple criteria

    use this array formula

    =INDEX(Sheet1!$C$2:$C$13,MATCH(1,(Sheet1!$B$2:$B$13=C1)*(Sheet1!$A$2:$A$13=C2),0))

    Confirm with Ctrl+Shift+Enter and not jsut Enter
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,898

    Re: Return Text if True based on multiple criteria

    Try this array formula...
    =INDEX(Sheet1!$C:$C,MATCH(Sheet2!$C$1&Sheet2!$C$2,Sheet1!$B:$B&Sheet1!$A:$A,0),1)

    ...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,898

    Re: Return Text if True based on multiple criteria

    Or this non-array formula with a helper column...

    in sheet1 D2, copied down...
    =B2&A2
    Then on sheet2...
    =INDEX(Sheet1!$C:$C,MATCH(Sheet2!$C$1&Sheet2!$C$2,Sheet1!$D:$D,0),1)
    entered in the regular way

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

    Re: Return Text if True based on multiple criteria

    Here's another one...

    Array entered**:

    =INDEX(Sheet1!C2:C13,MATCH(C1,IF(Sheet1!A2:A13=C2,Sheet1!B2:B13),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.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,898

    Re: Return Text if True based on multiple criteria

    1 of the things I LOVE about excel, there is never 1 right way to do something. 4 different ways to do a fairly simple task

  7. #7
    Registered User
    Join Date
    06-10-2013
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Return Text if True based on multiple criteria

    Thanks Ace_XL and FDibbins!

    Both worked on the sample sheet, but only FDibbins' method worked on my actual tracker.

    Either way, thank you both for your quick replies!

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

    Re: Return Text if True based on multiple criteria

    Quote Originally Posted by FDibbins View Post
    there is never 1 right way to do something.
    As long as they get the correct result they're all the right way.

    Now, if you want to know which way is the best way then that's a different story!

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,898

    Re: Return Text if True based on multiple criteria

    Happy to help and thanks for the feedback

    @ Tony, the best way is the 1 the OP understands and is able to re-create easily, I would say?

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

    Re: Return Text if True based on multiple criteria

    Quote Originally Posted by FDibbins View Post
    @ Tony, the best way is the 1 the OP understands and is able to re-create easily, I would say?
    Where I come from that's called dumbing it down.

    Hard to know how many OPs are interested in learning or do they just want to get an answer.

    For those that are here to learn we owe it to them to offer the best solution. At least, that's how I approach things.

  11. #11
    Registered User
    Join Date
    06-10-2013
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Return Text if True based on multiple criteria

    I am definitely interested in learning about the formulas, but at the same time, I do have deadlines and a tight schedule to maintain. So for me, the best way is the one where I can understand it quickly and re-create the results easily.

    Thanks to you all for helping!

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

    Re: Return Text if True based on multiple criteria

    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)

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