+ Reply to Thread
Results 1 to 6 of 6

find first occurrence in range on "small"

  1. #1
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,224

    find first occurrence in range on "small"

    hello

    looking for a formula that finds the "first occurence" based on the "smallest" or "min" number?

    i have attached my workbook. i tried to use index match but that simply gives me the "first instance" of the center # rather than the smallest or min it first occured in.

    pls help.

    this is my formula that is not incorporating "small":
    =INDEX($H$6:$I$15,MATCH(L6,$I$6:$I$15,0),1)

    i even tried the below it is doesnt work:
    =SMALL(INDEX($H$6:$I$15,MATCH(L6,$I$6:$I$15,0),1),1)
    Attached Files Attached Files
    Last edited by jw01; 08-01-2013 at 10:31 AM.

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: find first occurrence in range on "small"

    If I understand you rightly, use this array formula:

    =MIN(IF($I$6:$I$15=L6,$H$6:$H$15))

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: find first occurrence in range on "small"

    Checkout the array formula in M9:
    Attached Files Attached Files
    Gary's Student

  4. #4
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,224

    Re: find first occurrence in range on "small"

    thxs alot guys....seems simple and effective

  5. #5
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,224

    Re: find first occurrence in range on "small"

    hey guys

    my data set has changed and i tried to apply the same formula however it is not working.

    see attached workbook.

    my data set is big and my list of accounts are in column I and list of occurance is in column B (column B is reference to month i.e. P1 = jan or 1, P2 = feb or 2 etc).

    thxs
    Attached Files Attached Files

  6. #6
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: find first occurrence in range on "small"

    You didn't have text values in your previous sample for that column:

    Try:

    ="P"&SMALL(IF($I$4:$I$9763=I4,--SUBSTITUTE($B$4:$B$9763,"P","")),1)

    Again - array formula. And please make sure that all your entries in column B begin with an upper case P or this will not give the desired results. If you prefer them all to be lower case, change the "P" in the above to "p", but either way make them the same.

    Regards
    Last edited by XOR LX; 08-01-2013 at 11:33 AM.

+ 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] Dynamic Range For "SMALL" Function Then Offset LookUp
    By david.nicholls in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-08-2013, 07:15 AM
  2. Replies: 3
    Last Post: 04-14-2013, 11:53 PM
  3. [SOLVED] How to extract "large" and "small" amounts from vlookup
    By okjeep in forum Excel General
    Replies: 14
    Last Post: 05-02-2012, 06:28 AM
  4. Replies: 4
    Last Post: 12-26-2011, 05:09 PM
  5. Find nth occurrence and replace with ":"
    By marlea in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-05-2005, 05:43 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