Finding the MAX Value Using MATCH

[artiststevens]

Hello,
Can anyone help me with a creating lookup, match, or index formula, whichever works best for the situation?

I need a formula that looks for the MAX value in a column between two specific time periods 21:00 to 20:00 or every time the number in column “A” changes. The value would then repeat until the number in column “A” changed.

Please see attached spreadsheet for a better understanding.

Thanking you for taking the time to read this post and for any and all help you can provide.

[tigertiger]

Try the formula for cell E2
=IF(A2<>A1,MAX(D2:D26),E1)

then copy fill-down for E3,E4,... -> the last cell you want

[tigeravatar]

artiststevens,

Attached is a modified version of your sample workbook. Because each number in column A goes for a different number of rows, I decided to use an array formula to get the desired results. The attached workbook contains a named range: "rngNumber" which is cells A2 to the last populated cell in column A. In order to do so, rngNumber is defined with the following formula:
=INDIRECT("Sheet1!$A$2:$A"&ROW(OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A$1:$A$65000)-1,)))

With that named range setup, I then put the following formula in cell E2 and then copied down:
=MAX(IF(rngNumber=A2,OFFSET(rngNumber,,3),0))

Its important to note that the formula in cell E2 is an array formula and therefore requires that it be entered with Ctrl+Shift+Enter and not just enter

Hope that helps,
~tigeravatar

[6StringJazzer]

A somewhat simpler approach is a formula that you can put in E2 and then copy down:

Please Login or Register  to view this content.

This assumes that the numbers in column A increase by 1 each time they change. This is true in your example but I don't know if it's always true.

This would also require a dummy number to be added in column A after the last row that is equal to the last number in column A plus 1.

It will work for any number of values in each set.

[artiststevens]

A somewhat simpler approach is a formula that you can put in E2 and then copy down:

Please Login or Register  to view this content.

This assumes that the numbers in column A increase by 1 each time they change. This is true in your example but I don't know if it's always true.

This would also require a dummy number to be added in column A after the last row that is equal to the last number in column A plus 1.

It will work for any number of values in each set.

Thank you all of you for your help! I greatly appreciate it. 6StringJazzer I like the simplicity of your formula, but when I enter the formula it gives me the highest value from the current numbers close I want the previous numbers highest close.

The formula should begin at "E27" and should equal 0.9574 and then repeat, "E50" should equal 0.9584 and then repeat, and "E74" should equal 0.9566 and then repeat. Also, your correct in your assumption that each number in column "A" increase by 1.

See attached for what happens when I enter the formula. Thanking you again in advance for all of the help.

[artiststevens]

Hey 6StringJazzer,
I tooled around with your formula and I got it to work the way I needed it to. Thank you very much again for all of your help!

[tigertiger]

sorry, I make mistake in last post

but I think you can use the simple formula like that
at E27:
=IF(A27<>A26,MAX(D2:D26),E26)

and copy fill down to E28, E29,...