Hi,
What Formula will return the above request (Preferable as "simple" as possible)
Pls see sample in the attached Picture.
Thanks, Elmer
Hi,
What Formula will return the above request (Preferable as "simple" as possible)
Pls see sample in the attached Picture.
Thanks, Elmer
Last edited by ElmerS; 12-15-2008 at 07:28 PM.
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
... or =MATCH(2, 1/($A$2:$A$15=C2), 1), also an array formula.
Entia non sunt multiplicanda sine necessitate
Just from looking at it - it seems to be the simplest to be found.
Many thanks, Elm
That's certainly better.....
adapt like this to make it non-array
=MATCH(2,INDEX(1/($A$2:$A$15=C2),0))
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
Too good to be true,
Thanks.
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 ?
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.
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
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.... because there in no ascending order nor 1 at the end
Elmer, would you please post workbooks instead of pictures?
Last edited by shg; 12-15-2008 at 08:15 PM.
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
Thank you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks