+ Reply to Thread
Results 1 to 3 of 3

INDEX MATCH SMALL likely function to find nth occurrences in a column

  1. #1
    Forum Contributor
    Join Date
    03-30-2020
    Location
    Wales, UK
    MS-Off Ver
    365 (Mac)
    Posts
    191

    INDEX MATCH SMALL likely function to find nth occurrences in a column

    Hi,

    Please see attached link (and attachment), where I am trying to find the first 10 unique events that begin with the letter 'S' as well as the dates on which they were first completed. I need to search the range C3:C2002 in the 'All Completed Runs' worksheet and return the relevant events and dates in columns B and C of the 'All Completed Runs - Ssssnakes' worksheet.

    I thought that some sort of Index Match or Index Small function would do this, but I am really struggling to find a solution, if anyone might be able to help.

    https://1drv.ms/x/s!AtIvkRgReIvwgT0I...qw40W?e=hzzQed

    Thanks!

    Also shared on Mr. Excel forum: https://www.mrexcel.com/board/thread...olumn.1130402/
    Attached Files Attached Files

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,623

    Re: INDEX MATCH SMALL likely function to find nth occurrences in a column

    A and B3 and copy down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    A is a regular formula, B is an array one (committedd with Ctrl+Shift+Enter)
    You may use in B3 unlimited range version,
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    but it will probably slow down calculations in your workbook, which is probably much more complicated than the piece you attached (I even cut it more for tests). I don't expect such range limiting would be needed for A formula, because in your version of Excel, the calculations algorithm of Excel shall limit the used range itself.

    Note that because of removing duplicates by checking if any of earlier runs is already listed with MATCH, the formula always looks for the SMALL(… ,1), but MIN can't be used here, as it would return 0 as 4th date, while SMALL returns an error, which can be easily handled .
    Attached Files Attached Files
    Best Regards,

    Kaper

  3. #3
    Forum Contributor
    Join Date
    03-30-2020
    Location
    Wales, UK
    MS-Off Ver
    365 (Mac)
    Posts
    191

    Re: INDEX MATCH SMALL likely function to find nth occurrences in a column

    Thanks, that's great

+ 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 for duplicate values without using SMALL function.
    By kengy428 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-02-2019, 12:59 PM
  2. [SOLVED] Combining INDEX/MATCH with several criteria and INDEX/SMALL
    By Dresas in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-15-2018, 11:02 AM
  3. Replies: 2
    Last Post: 07-03-2017, 10:09 AM
  4. [SOLVED] COLUMN find formula for index, small
    By Berna11 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-15-2016, 05:51 AM
  5. [SOLVED] tie scores (stopping/skipping duplicates) using index/match plus small function
    By Mrjpjones in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-06-2016, 02:13 PM
  6. [SOLVED] Help Requested with Nested Small Function Inside Index/Match Formula
    By trandle in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-26-2015, 10:07 AM
  7. VBA index match with multiple occurrences
    By jwlamb in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 11-26-2014, 11:04 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