lookup

[Guest]

I am trying to use a Lookup function using vectors to do the following:
I have a date/value times series of river flows in a columnar format:
1978 1979 1980 ...
4/1 15 16 5
4/2 20 45 10
4/3 25 30 15

peak 25 45 15

etc...

I have computed the peak flow using the Max function and I want to be able
to lookup the date corresponding to the computed peak flow for each year. I
tried to use the LOOKUP function with vectors but I realized that it only
works when the list is sorted which is not the case in my dataset. Is there a
way around this ?

Thanks

 

[N Harkawat]

=index(a1:a100,match(max(b1:b100),b1:b100,0))
assuming a1:a100 holds dates and b1:b100 holds the value
However if there are more than 1 max value it will show the date which comes
first

 

[Guest]

THANKS A LOT!

=index(a1:a100,match(max(b1:b100),b1:b100,0))
assuming a1:a100 holds dates and b1:b100 holds the value
However if there are more than 1 max value it will show the date which comes
first