return max date from list

[GS]

I thought this should be easy enough, but it's been years since I've
done serious excel work and I have gone into brainlock trying to figure
this out..

What I want to do is, find the date of the last occurance of a value, in
this case a number. For example:


A B C D E
1 23 32 44 01-JUL-03
2 3 7 56 03-AUG-04
3 12 34 45 23-OCT-05
4 23 26 58 24-MAR-06
5 15 37 79 23-JUN-06
6 11 32 39 28-DEC-06


I have a list of all possible numbers in another location, 1 thru 200,
and I want to put a formula beside each number that will find the last
date that number occurred in the array, in any of the columns.

Is this possible? I was thinking of vlookup, but will that not just
finde the first occurance (I guess I could sort the list with descending
dates then), but can one use vlookup to find a number in any one of 2 or
more columns?

thanks!

 

[Don Guillett]

This is an array formula that must be entered/edited using ctrl+shift+enter
vs just enter
where e1 contains the number sought
=MAX(IF(A38=E1,A3:E8))