Index........match.............max!

Greeting All,

I have a challenge that seems like too much for me to handle and google search is not helping.

I have 2 worksheets and one has a daily record of events (5 columns, starting with date, start value, high, low and end value, lets call this “data” all the values recorded are numbers or price fluctuation), I have constructed another simple worksheet and want to locate and link values from my database on the second sheet.

In a simple form on sheet two I have 2 dates which the second one is always greater than first one (B2 and C2). I would like to create a formula in Cell B3 and B4 to place the MAX value and the corresponding date from “Data” worksheet
I would like to look up my “data” between these 2 dates and find the highest price in column 3 and also the corresponding date in the same raw(in column 1)…..hopefully that makes sence!

Thanks in advance

If you could attach a sample of your workbook, it would be most helpfull. You can do this by clicking on Go Advanced>Manage Attachments>Browse>Open>Upload File>Close Window>Submit Reply. Thanks.

in B5

=MAX(IF((Data!$A:$A>='6yr Cycle'!$B$2)*(Data!$A:$A<='6yr Cycle'!$C$2),Data!$C:$C))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

in B4

=INDEX(Data!A:A,MATCH('6yr Cycle'!C5,Data!C:C,0))

Thanks John. The Max worked mind you kept pressing enter!

Somehow the Index comes up #N/A!

Worked OK for me on your posted file.

MAX should be entered as an array formula as per post.

See attached

When I evaluate this formula
=INDEX(Data!A:A,MATCH('6yr Cycle'!C5,Data!C:C,0))
when it reaches to =INDEX(Data!A:A,MATCH('6yr Cycle'!C5,Data!C:C,0))
then the next part changes that to 0

Of course I have already done the Max and there is a value of 3992.64 there!

Oh it need to be B5!!

your previous post indicated :
=INDEX(Data!A:A,MATCH('6yr Cycle'!C5,Data!C:C,0))

and your input in the excel was
=INDEX(Data!A:A,MATCH('6yr Cycle'!B5,Data!C:C,0))

C5 is empty at the moment! LOL

Great. I think it is working. I am just going to see if I can fill the other rows as well quickly. I'll keep you posted.

Thanks John

Thank you, thank you, thank you John.

I just entered those formula in over 700 cells each. I have been trying for over a week to come up with some model and although the journey is just starting, I was stuck at this part.

the formula was much simpler than I thought. Clearly I need to take some courses! LOL

Regards
Amin

No gain without pain ... we have all been there!!!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.