Match calender week and return last date value

[Nanogirl21]

Can someone please help me adjust my formula to pull the data from the last date in the matching calendar week.

My current formula pulls the max value from the matching calendar week.
=MAX(IF($E$3:$NE$3=E$13,$E8:$NE8)

$E$3:$NE$3 = Calendar week range to match
$E$4:$NE$4 = Date (mm/dd/yyyy)
E8:$NE8 = Data to pull
E$13 = Calendar week

I would like to adjust the formula to match calendar weeks (row 3 & row 13) and pull the value from the last date (row 4) of that corresponding calendar week. The calendar weeks and dates are not always in order.

Thank you

 

[arthurbr]

Are you using normal or ISO weeks?

 

[Nanogirl21]

Are you using normal or ISO weeks?

ISO Week Numbers

 

[Nanogirl21]

Any suggestions?

 

[Nanogirl21]

Nevermind. I was able to figure it out.

Just in case someone comes across this. =LOOKUP(2,1/($E$3:$NE$3=E$13), $E8:$NE8) was able to solve my problem.