Text in cell next to max value

[gnoke]

Hi,

Thanks to NBVC I use this formula to find the max value between to specific dates:

=MAX(INDEX(Hist!B:B,MATCH($B$2,Hist!A:A,0)):INDEX(Hist!B:B,MATCH($C$2,Hist!A:A,0)))
Where column A in sheet "Hist" consist of dates, and coulumn B in "Hist" consists of values.

Now, I would really like to display which date that holds the maximum value. Ex. If the max value is in B400 I would like to show the date in A400.

I do not think I can use a MAX formula, because the sheet contains about 40 000 values, and the probability for the same value appearing twice or more is significant.

Is there any such formula, and can someone please assist me in writing it?

Gnoke

[teylyn]

I do not think I can use a MAX formula, because the sheet contains about 40 000 values, and the probability for the same value appearing twice or more is significant.

So, if the maximum value is present more than once, what would you expect to see?

[gnoke]

Hi and thanks for replay,

I would expect to get an error message?
But maybe, and hopefully I am wrong
Regardless, my skills are so limited, that I have not tried any MAX formulas, due to I am unsure which to try..

Gnoke

[teylyn]

So, if there are several cells that have the same max value, you want to see an error message instead of the date in column A?

Do I read this right?

Can you post a workbook with a small data sample, so we have something to go by, please?

[gnoke]

Thanks again,

I have attatched a sample.

The ideal would perhaps be to lookup in the same date-range as the MAX formula in B4?

Gnoke

[DonkeyOte]

Using your sample - for simplicity:

Please Login or Register  to view this content.

you might wish to add a pre-emptive test to handle possibility of start date preceding first date listed on hist sheet

[gnoke]

Thanks!

I can see it work prefectly for the sample!
In my original sheet however, I must have done something wrong, maybe you could easily see what I have done wrong.

The formula looks like this for me:

=MATCH(A7,'[sample.xls]SAMPLE 2010- smpl'!B:B)+(LOOKUP(A7,'[sample.xls]SAMPLE 2010- smpl'!B:B)<>A7)

A7 equals date as B2 does in sample. The dates in reference sheet is in column B.
As cell result I get 35 521, which is the first row with no dates (last date is in row 35 520).
Therefore I assume I have broken down the formula, sorry

I totally understand if my low skills tires you, and therefore there is little you can do. However I hope the fact that I get the first empty cellnumber makes it easy for you to spot what I have messed up

Gnoke

[DonkeyOte]

If the date in A7 exceeds the Max Date listed in Col B of the source file then you would get your 35521 result.

Note: above is based on assumption that data is sorted by date in the source file as per your sample

[gnoke]

Of course, now I feel pretty smart...:P

Thanks DonkeyOte