Finding the maximum value in an index/match formula

[Triedtwice]

Hello Excel Masters...I have the following index and match formula to find the "Daily_Total" value associated with a "Total_Date" value:
=IFERROR(INDEX(DAILY_TOTAL,MATCH(B569,TOTAL_DATE,0)),AVERAGE(I539:I566))

I just experienced a first ... I have two "Daily_Total" values that are associated with a single "Total_Date" value. Is it possible to incorporate MAX into the formula to pull the maximum Daily_Total value for a given Total_Date into the index/match formula?

Thanks in advance for your expert opinions!

 

[Marcelo Branco]

Hi

I'm not 100% sure about what you are looking for, but i think it is possible.
Some data for testing purposes would be helpful - could you provide a small data sample, say 5 to 10 rows. and expected result(s)?


M.

 

[Triedtwice]

Here's the data giving me the problem:
[TABLE="width: 136"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"]2/16/2016[/TD]
[TD] 19.21[/TD]
[/TR]
[TR]
[TD="align: right"]2/16/2016[/TD]
[TD="align: right"]22.46[/TD]
[/TR]
[TR]
[TD="align: right"]2/15/2016[/TD]
[TD="align: right"]46.92[/TD]
[/TR]
[TR]
[TD="align: right"]2/14/2016[/TD]
[TD="align: right"]27.27[/TD]
[/TR]
[TR]
[TD="align: right"]2/13/2016[/TD]
[TD="align: right"]15.01[/TD]
[/TR]
[TR]
[TD="align: right"]2/12/2016[/TD]
[TD="align: right"]77.78[/TD]
[/TR]
[TR]
[TD="align: right"]2/11/2016[/TD]
[TD="align: right"]100.52[/TD]
[/TR]
[TR]
[TD="align: right"]2/10/2016[/TD]
[TD="align: right"]11.09[/TD]
[/TR]
</tbody>[/TABLE]

As you can see, there are 2 values for 2/16/2016. My formula finds and uses only the first one, but I need it to use the maximum value...hope this helps clarify.

 

[Triedtwice]

Marcelo, maybe it would also help to let you know that I defined ranges: TOTAL_DATE is the first column (dates) and DAILY_TOTAL is the second column (values). And thanks for your very quick response!!!

 

[Marcelo Branco]

See if this does what you want


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Dates​

[/td][td]

Values​

[/td][td][/td][td]

Search​

[/td][td]

Max​

[/td][/tr]


[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

02/16/2016​

[/td][td]

19,21​

[/td][td][/td][td]

02/16/2016​

[/td][td]

22,46​

[/td][/tr]


[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

02/16/2016​

[/td][td]

22,46​

[/td][td][/td][td][/td][td][/td][/tr]


[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

02/15/2016​

[/td][td]

46,92​

[/td][td][/td][td][/td][td][/td][/tr]


[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

02/14/2016​

[/td][td]

27,27​

[/td][td][/td][td][/td][td][/td][/tr]


[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

02/13/2016​

[/td][td]

15,01​

[/td][td][/td][td][/td][td][/td][/tr]


[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

02/12/2016​

[/td][td]

77,78​

[/td][td][/td][td][/td][td][/td][/tr]


[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

02/11/2016​

[/td][td]

100,52​

[/td][td][/td][td][/td][td][/td][/tr]


[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

02/10/2016​

[/td][td]

11,09​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

Put the date of interest in D2

Formula in E2
=MAX(INDEX((TOTAL_DATE=D2)*DAILY_TOTAL,0))

Hope this helps

M.

 

[Triedtwice]

Thank you very much, but I was hoping it would be possible to modify my existing formula, the results of which are used by other formulae in my workbook. Any one know if this is possible?

 

[Marcelo Branco]

Thank you very much, but I was hoping it would be possible to modify my existing formula, the results of which are used by other formulae in my workbook. Any one know if this is possible?

I didn't grasp your goal with the formula in post #1.
Why AVERAGE(I539:I566) in the IFEEROR part? How is it related with the MAX value? What would be the expected result with the data sample above?
Sorry, many questions...

M.

 

[Marcelo Branco]

Reviewing your initial message maybe I have understood, now, what you want, i.e.: if the date (B569) is not found in the range TOTAL_DATE, then the formula must return AVERAGE(I539: I566).

If I'm right, try this
=IF(ISNUMBER(MATCH(B569,TOTAL_DATE,0)),MAX(INDEX((TOTAL_DATE=B569)*DAILY_TOTAL,0)),AVERAGE(I539:I566))

M,

 

[Triedtwice]

You are correct and your suggestion appears to work like a charm!!! Thank you so much!

 

[Marcelo Branco]

You are correct and your suggestion appears to work like a charm!!! Thank you so much!

You are welcome. Glad to help

M.