0

I have tried all possible formulas I know to fetch maximum value where the available record date = current record date using both the index formula combined with the max function and likewise used vlookup but all to no avail. Below are the formulas.

=VLOOKUP(MAX(A2:A40), A2:C40, 0) , =INDEX(c2:C46,MATCH(MAX(a2:a40),a2:a40,0))

c2:c40 represents my amount column while a2:a40 represents my date column in the table while the current date = 5/12/2022

I have tried using if statements with max function and also used index()combined with match() and max() and vlooup/max()

King
  • 1
  • 2

1 Answers1

1

You can use the following:

=MAXIFS(C2:C40, A2:A40, DATE(2022,12,5))

Assuming the date of your sample is in dd/mm/yyyy format. Instead of DATE, you can refer to the corresponding cell with the date value.

David Leal
  • 6,373
  • 4
  • 29
  • 56
  • Did that already it still failed – King Dec 06 '22 at 13:34
  • @King you must be doing some error the previous approach should work in most of the excel version. Share a link to your file with edit permission, so I can take a look at it, to see what is going on. Thanks – David Leal Dec 06 '22 at 15:58
  • 1
    Thanks, David Leal, I was able to later find the MAXIMUM value using this formula =IF(I4 = A2:A40, MAX(C2:C40)) and it worked. I4 here is where my date is stored to compare against all dates available in my date column. while C2:C40 contains the amount to be checked for maximum value. – King Dec 07 '22 at 07:37