Excel Forumla Help - Find Most Recent Occurence of a Number

[CWatsonJr]

I am sorry if this has been covered before, I did a search and didn'
see anything applicable.

I have a table with 5 columns and currently 10 rows, but the rows wil
grow in the future. I need to find the most recent occurence of
number without having to manually scan the table.

The numbers are entered in a row like such:

A B C D E
1 Date Entry 1 Entry 2 Entry 3 Entry 4
2 2/27/04 125 4 200 56
3 2/26/04 8 56 46 32
4 2/25/04 3 45 122 86
5 2/24/04 32 126 85 121

Say for example, I need to find the latest occurence of the number 3
(E3). I need a forumla that would return 2/26/04 (A3).

I hope my question is clear enough and thanks advance for an
assistance with this.

Cliff Watso

 

[Frank Kabel]

Hi
try the following array entered formula (CTRL+SHIFT+ENTER)
=INDEX(A1:A10,MAX(IF(B2:E10=33,ROW(B2:E10),0)))

 

[CWatsonJr]

Thanks! That works!

I had to change the A1 to A2 because it returned the header "Date"
(A1).

Thank You again!!!!

Cliff Watson

 

[Bob Phillips]

Cliff,

Give this a try

=SUMPRODUCT(MAX((B1:B100)*(C1:E100=32)))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[CWatsonJr]

Wow - thanks again!

That works too...

I did have to change it to:

=SUMPRODUCT(MAX((A2:A100),(B2:E100=32)))


Thank you all!!!!!!

Cliff Watso

 

[CWatsonJr]

LOL...ooops...

I had to change it to...

=SUMPRODUCT(MAX((A2:A100)*(B2:E100=32)))

to get it to work...


Sorry for the confusion and thank you very much!