+ Reply to Thread
Results 1 to 6 of 6

Index formula looking at two criteria to find result

  1. 09-18-2015, 02:24 PM #1
    ciresuark
    ciresuark is offline
    Registered User
    Join Date
    12-12-2013
    Location
    Wisconsin
    MS-Off Ver
    Excel 2010
    Posts
    92

    Index formula looking at two criteria to find result

    I currently have a table with the following information. This is A1:C5.
    Name Action Question
    Bobby In Progress Question 1
    Jake Comleted Question 2
    Jake In Progress Question 3
    Frank In Progress Question 4

    In Cells F3 I am searching "Jake" and F4 "In Progress"

    I am currently trying to use in G4 
    Please Login or Register  to view this content.
    to return the question that is in progress for Jake.

    I am trying to make this basic for here to be able to match to the data I currently have.

    Any help is appreciated.

    At this time I cannot upload a document.
    Reply With QuoteRegister To Reply
  2. 09-18-2015, 02:30 PM #2
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Index formula looking at two criteria to find result

    Try this array formula**:

    =IFERROR(INDEX(C2:C5,MATCH(1,(A2:A5=F3)*(B2:B5=F4),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.
    Reply With QuoteRegister To Reply
  3. 09-18-2015, 02:51 PM #3
    ciresuark
    ciresuark is offline
    Registered User
    Join Date
    12-12-2013
    Location
    Wisconsin
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Index formula looking at two criteria to find result

    This worked; however, there are instaces on my document which I am converting this formula to fit where a person (Jake) may have more than one question "In Progress". I think this is why I was using "Small" in my formula but I am unsure.

    So in the example I am trying to fill the formula to H4, I4...
    Reply With QuoteRegister To Reply
  4. 09-18-2015, 02:57 PM #4
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Index formula looking at two criteria to find result

    This array formula** entered in H4:

    =IFERROR(INDEX($C:$C,SMALL(IF(($A2:$A5=$F3)*($B2:$B5=$F4),ROW($B2:$B5)),COLUMNS($H4:H4))),"")

    ** 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 across until you get blanks.
    Reply With QuoteRegister To Reply
  5. 09-18-2015, 03:15 PM #5
    ciresuark
    ciresuark is offline
    Registered User
    Join Date
    12-12-2013
    Location
    Wisconsin
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Index formula looking at two criteria to find result

    That is it! Thanks for the help!
    Reply With QuoteRegister To Reply
  6. 09-18-2015, 03:18 PM #6
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Index formula looking at two criteria to find result

    You're welcome. Thanks for the feedback!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Index/Match Multiple Criteria only Finds First Result
    By DomSza in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-12-2015, 08:57 AM
  2. [SOLVED] Multiple criteria for Index and Match with if(and( & if(or( result #value?
    By v.rodgers in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-08-2015, 12:52 AM
  3. Convert a formula result to text so another formula can find that result
    By rwoollams in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-12-2014, 01:49 AM
  4. excel formula to find the result from the 3 column with criteria
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-26-2013, 05:09 AM
  5. [SOLVED] Formula whit INDEX and MATCH. I try to make this formula to result.
    By Anka in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-28-2012, 06:26 PM
  6. Find Value If Multi Criteria Matches Index
    By exc4libur in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-05-2011, 09:05 PM
  7. Index formula returning incorrect result
    By Nadir Soofi in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-18-2008, 02:08 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1