# Return the Last Occurrence of a Number in Range

1. ## 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

2. 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. ... or =MATCH(2, 1/(\$A\$2:\$A\$15=C2), 1), also an array formula.

4. Just from looking at it - it seems to be the simplest to be found.

Many thanks, Elm

5. That's certainly better.....

adapt like this to make it non-array

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

6. 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. Too good to be true,

Thanks.

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

11. ... 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?

12. 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. Originally Posted by ElmerS
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. Thank you.

#### Thread Information

##### Users Browsing this Thread

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

#### 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