+ Reply to Thread
Results 1 to 15 of 15

Index/Match - More than 1 Result

  1. #1
    Registered User
    Join Date
    09-25-2015
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    29

    Index/Match - More than 1 Result

    I have attached an example of what I am trying to do.

    So the problem is, the smallest 2 numbers returns the same value so for the Person, it returns only the first result twice for those two cells.

    How do I set it to display both Sandy and Maple? And is it possible for all the formulas to be set universally to expect ties?
    Attached Files Attached Files
    Last edited by LJenny; 10-09-2015 at 03:54 PM.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,072

    Re: Index/Match - More than 1 Result

    Can you upload an example worksheet? (Go Advanced>Manage Attachments)
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

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

    Re: Index/Match - More than 1 Result

    Can you post a SMALL sample file and show us what result you expect?

    A SMALL file will have about 20 rows worth of data.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    09-25-2015
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    29

    Re: Index/Match - More than 1 Result

    Quote Originally Posted by ChemistB View Post
    Can you upload an example worksheet? (Go Advanced>Manage Attachments)
    Quote Originally Posted by Tony Valko View Post
    Can you post a SMALL sample file and show us what result you expect?

    A SMALL file will have about 20 rows worth of data.


    I have attached a sample file. ^^

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,615

    Re: Index/Match - More than 1 Result

    Try array entering this formula in C11 and fill down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

    The formula you used is great for single matches. The MATCH function returns only the first match it finds in cases like this, and that's all unfortunately.
    Dave

  6. #6
    Registered User
    Join Date
    09-25-2015
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    29

    Re: Index/Match - More than 1 Result

    Hi thanks so much!

    This formula works on my sample spreadsheet but when I extrapolate the formula to my actual spreadsheet, it is returning the same value :S

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

    Re: Index/Match - More than 1 Result

    Make sure calculation is set to Automatic.

  8. #8
    Registered User
    Join Date
    09-25-2015
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    29

    Re: Index/Match - More than 1 Result

    Yup it is set to automatic.

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

    Re: Index/Match - More than 1 Result

    Can you post a small sample file that demonstrates your problem?

    20 rows worth of data is plenty.

  10. #10
    Registered User
    Join Date
    09-25-2015
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    29

    Re: Index/Match - More than 1 Result

    Quote Originally Posted by Tony Valko View Post
    Can you post a small sample file that demonstrates your problem?

    20 rows worth of data is plenty.
    Ah k, here's a better sample.

    So on the summary worksheet, that's what I am trying to do.
    Attached Files Attached Files

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

    Re: Index/Match - More than 1 Result

    This array formula** entered in C4 and copied down:

    =INDEX('Validation Data'!A:A,SMALL(IF('Validation Data'!G$3:G$50=A4,ROW('Validation Data'!G$3:G$50)),COUNTIF(A$4:A4,A4)))

    ** 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.

  12. #12
    Registered User
    Join Date
    09-25-2015
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    29

    Re: Index/Match - More than 1 Result

    Awesome! This works perfectly! Thanks!!

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

    Re: Index/Match - More than 1 Result

    You're welcome. Thanks for the feedback!


    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

  14. #14
    Registered User
    Join Date
    09-25-2015
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    29

    Re: Index/Match - More than 1 Result

    Marked as solved

    Oh quick question, one of the results is coming up at #NUM, any idea why?
    Last edited by LJenny; 10-13-2015 at 11:02 AM.

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

    Re: Index/Match - More than 1 Result

    That usually means there is not an nth instance of the lookup value.

    Typically, we would use an error trap to prevent the error.

    In Excel 2003, that formula would be:

    =LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX('Validation Data'!A:A,SMALL(IF('Validation Data'!G$3:G$50=A4,ROW('Validation Data'!G$3:G$50)),COUNTIF(A$4:A4,A4)))))

    Still array entered.

+ 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. [SOLVED] Index/Match wrong result
    By spinkung in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-14-2015, 05:20 AM
  2. [SOLVED] Index/Match result should again search for zero result
    By ursanil in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-07-2014, 04:45 AM
  3. [SOLVED] index and match function result from three way look up
    By johnodys in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-15-2013, 03:32 AM
  4. index match, ..... skip result if blank value ...... jump to next match
    By gehawk in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-08-2013, 04:42 AM
  5. [SOLVED] Index Match if result is not something
    By Jaron_t in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-19-2012, 01:19 PM
  6. index match result issue
    By cedarhill in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-20-2011, 01:18 PM
  7. Index/Match Returning N/A Result
    By Killer17 in forum Excel General
    Replies: 1
    Last Post: 01-09-2009, 11:15 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