+ Reply to Thread
Results 1 to 14 of 14

Return the Last Occurrence of a Number in Range

  1. #1
    Forum Contributor
    Join Date
    11-28-2008
    Location
    Reykjavík, Iceland
    MS-Off Ver
    Any of: 2003 & 2007
    Posts
    412

    Return the Last Occurrence of a Number in Range

    Hi,

    What Formula will return the above request (Preferable as "simple" as possible)

    Pls see sample in the attached Picture.

    Thanks, Elmer
    Attached Images Attached Images
    Last edited by ElmerS; 12-15-2008 at 07:28 PM.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    One way is to use this "array formula" in d2 copied down

    =MAX(IF(A$2:A$15=C2,ROW(A$2:A$15)-ROW(A$2)+1))

    confirmed with CTRL+SHIFT+ENTER

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    ... or =MATCH(2, 1/($A$2:$A$15=C2), 1), also an array formula.
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Forum Contributor
    Join Date
    11-28-2008
    Location
    Reykjavík, Iceland
    MS-Off Ver
    Any of: 2003 & 2007
    Posts
    412
    Just from looking at it - it seems to be the simplest to be found.

    Many thanks, Elm

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    That's certainly better.....

    adapt like this to make it non-array

    =MATCH(2,INDEX(1/($A$2:$A$15=C2),0))

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Since suggesting a more compact formula than DLL's would be a first, you might want to wait on that

    Would you please mark the thread solved?

    To mark a thread as solved:

    Click the Edit button on your first post in the thread

    Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes

    Edit: and there you have it

  7. #7
    Forum Contributor
    Join Date
    11-28-2008
    Location
    Reykjavík, Iceland
    MS-Off Ver
    Any of: 2003 & 2007
    Posts
    412
    Too good to be true,

    Thanks.

  8. #8
    Forum Contributor
    Join Date
    11-28-2008
    Location
    Reykjavík, Iceland
    MS-Off Ver
    Any of: 2003 & 2007
    Posts
    412
    Shg,

    You must understand that I had to Calm down after these great responses.

    I assume I marked it as "SOLVED" while you were responding.

    I would like to take this opportunity to ask daddylonglegs:

    What the 2 stands for in the "MATCH(2,INDEX" part ?

  9. #9
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    With no exact match, and an ascending order selected (by the 1 at the end, which is also the default) MATCH returns the largest value less than the sought value, and the last among equals.

  10. #10
    Forum Contributor
    Join Date
    11-28-2008
    Location
    Reykjavík, Iceland
    MS-Off Ver
    Any of: 2003 & 2007
    Posts
    412
    Sorry, but I did not follow you - because there in no ascending order nor 1 at the end...

    However, here is my last question to close this subject.

    Hoe can the last number as per class "A" be returned ?

    Attached pls find another picture.

    Thanks for all your efforts, guys,

    Elm
    Attached Images Attached Images

  11. #11
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    ... because there in no ascending order nor 1 at the end
    Take a look at Help for the MATCH function. The last arument specifies how the function behaves, and it defaults to 1 if not specified. So there is implicitly at 1 at the end.

    Elmer, would you please post workbooks instead of pictures?
    Last edited by shg; 12-15-2008 at 08:15 PM.

  12. #12
    Forum Contributor
    Join Date
    11-28-2008
    Location
    Reykjavík, Iceland
    MS-Off Ver
    Any of: 2003 & 2007
    Posts
    412
    Hi,

    After a good night sleep I examined the Match function and:

    1) The 1, as the Match_type, was unnecessary in your first Array-Formula.

    2) The lookup value of 2 confused me at first glance.
    Now I understand that it could also be 1.000001 to return the correct result.

    Again, thanks a lot, Elm

    PS: a WB will be attach net time

  13. #13
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Quote Originally Posted by ElmerS View Post
    Hi,

    After a good night sleep I examined the Match function and:

    1) The 1, as the Match_type, was unnecessary in your first Array-Formula.

    2) The lookup value of 2 confused me at first glance.
    Now I understand that it could also be 1.000001 to return the correct result.

    Again, thanks a lot, Elm

    PS: a WB will be attach net time
    1) True, but possibly better to include it because it makes the intent clearer, a missing argument could just be forgetfulness

    2) True. 2 is just a convention in some of these forums

  14. #14
    Forum Contributor
    Join Date
    11-28-2008
    Location
    Reykjavík, Iceland
    MS-Off Ver
    Any of: 2003 & 2007
    Posts
    412
    Thank you.

+ 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