find last row data [for the particular]/[filtered

问题:

In excel I have data as below.

2011    0
2011    15
2011    10
2011    5
2012    15
2012    10
2012    5

What I want to find is the last row for the respective year so that the output would be

2011    5
2012    5

Any idea how to get this done?

I am looking at this post and found the way for finding last row data, however I need to know how to filter the same by year.

Note: I want to do this using excel pre-defined function. NO macro, NO coding, NO pivot.

---

Edit 1

=INDEX($B:$B,MAX(IF(LEN($B:$B)>0,ROW($B:$B),0)),1) gives me answer 5. However I want it to fiter by year. Any idea how to get this done?

---

Edit 2

=OFFSET(B2,MAX(IF(NOT(ISBLANK(B2:B25)),ROW(B2:B25),0))-ROW(B2),0) this also gives last row data.

回答1:

Is this what you want?

{=INDEX($B$1:$B$7,MAX(IF(A9=$A$1:$A$7,ROW($A$1:$A$7))))}

You must enter this as an array function, so enter copy and paste the code between the {}'s and then press ctrl-shift-enter. The code assumes you have 2011 in cell A9. If you then type 2012 in A10 and copy the formula down it will work.

The basic idea of the formula is:

1. If any cell in A1:A7 matches A9, add its row number to a list of candidate rows
2. Take the maximum row number of the list of candidates; say this is k
3. Use the index function to return the value that is in row k of B1:B7

回答2:

The easiest way to do this without coding is by using pivot tables.

If you did happen to write an excel formula or function for it you would most likely use the max() function and the vlookup() or hlookup() functions.