Return Value based on max date

[Steves73]

Hi All

This one has got me

Trying to find a formula to be put in cell C2 that looks in column B, finds the first min date based on the month reference in A2 (Note some dates repeat) in this case 03-Feb-24 in Cell B10 as this is the lowest and first data set typed in and then returns it, the formula uses "Feb-24" in Cell A2 as the reference for which give the formula the month to look for. If we can It would be great to have the adjacent value in Column C returned in C3, in this case EGP 68,470.00

Cell D4 will need the same but this looks for the highest date value within the month and again returns the adjacent value from column C

I Hope I have explained this correctly

 

[Cubist]

Try
C2:

=MINIFS(B6:B29,A6:A29,A2)

D2:

=MAXIFS(B6:B29,A6:A29,A2)

C3:

=VLOOKUP(C2,$B$6:$C$29,2)

D3:

=VLOOKUP(D2,$B$6:$C$29,2)

 

[DanteAmor]

MIN values

Cell C2:

=MIN(FILTER(B6:B29,MONTH(B6:B29)=MONTH(A2)))

Cell C3:

=XLOOKUP(C2,B6:B29,C6:C29)

MAX values
Cell D2:

=MAX(FILTER(B6:B29,MONTH(B6:B29)=MONTH(A2)))

Cell D3

=INDEX(C6:C29,MAX(FILTER(ROW(B6:B29),B6:B29=D2))-ROW($C$6)+1)

 

[Steves73]

Thanks mate, Perfect, thanks for your help.

As a couple of dates repeat (3-Feb-24) the Vlookup picks up the second one, I tried Xlookup and that seemed to work

 

[Fluff]

Another in C2 only

=TOCOL(TAKE(FILTER(B6:C100,MONTH(B6:B100)=MONTH(A2)),1))

and in D2 only

=TOCOL(TAKE(FILTER(B6:C100,MONTH(B6:B100)=MONTH(A2)),-1))

 

[Steves73]

Thanks all

All of your suggestions worked well, thanks again

 

[Fluff]

Glad we could help & thanks for the feedback.