Last occurrence in range

[staroslav]

How do I locate the latest date an item (name) had been listed in a range?

[pre]

eg:
A     |   B   |   C
1 Date     | Name1 | Name2 ...
2 01/01/12 | item1 |
3 01/03/12 |       | item1
4 01/06/12 |       |

[/pre]
What is the formula that will search for item1 and give me 01/03/12?

Thanks! Tried to play around with vlookup and max, but my excel knowledge is very limited...

 

[staroslav]

I think index and match functions are needed here, but so far can't make it work. Anybody? this shouldn't be too difficult...

Thanks!

 

[Krishnakumar]

Hi


Try this array formula.


=INDEX(A2:A4,MAX(IF(B2:C4=E3,ROW(A2:A4)-ROW(A2)+1)))


where E3 holds item1


Kris

 

[staroslav]

Hello Krishnakumar!

Thank you for your response. I get a #VALUE! error. Some cells in range don't have data, is that the problem?

My formula looks this way at present (working in mac office excel):


=INDEX(A2:A54;MAX(IF(srange=B72;ROW(A2:A54)-ROW(A2)+1)))


where srange is a named range I use; B72 contains (item1) what I am looking for...

 

[Hui]

Staroslav


When you entered the formula you need to press Ctrl+Shift+Enter not Enter

 

[staroslav]

Thank you very much! Found that this formula also works =SUMPRODUCT(MAX((A2:A54)*(srange=B71)))

Again, THANK YOU!

 

[Debraj]

Hi Staroslav,


As you are the questionnaire, then its depend upto you that, what you define as "last Occerence"..

as per you answer "Last Occurance" is Max Date,

but Krishna's Formula gives you "Last Occurance" in "Column A"


still, glad you solved it..


PS:

Just to reduce the length of your formula and giving credit to Krishna's Formula..

{=MAX(IF(srange=B71,A2:A54))}

you need to press Ctrl+Shift+Enter not just Enter

Regards,

Deb