How to condense a nested MIN/MAX formula in IF statement

[JamesonMH]

Hi all,

I want to somehow condense the red and blue sections below so my formula isn't so bulky.

Background: this formula is returning the first or last invoice date for select products depending on drop-down selection. All works fine... But

Is there a way to condense this so I don't have to re-write majority of formula over just for 1 criteria change (i.e. IF cell E638 says "Last" then MAX.... vs. IF "First" then MIN)

{=INDEX($F$642:$F$648,SUMPRODUCT(IF(E638="Last",MAX(IF($E$642:$E$648=E637,($E$642:$E$648=$E$637)*ROW($E$642:$E$648)))-ROW($F$642)+1,MIN(IF($E$642:$E$648=E637,($E$642:$E$648=$E$637)*ROW($E$642:$E$648)))-ROW($F$642)+1)),0)}


Thanks,
James

 

[Marcelo Branco]

Maybe...

=INDEX($F$642:$F$648,LARGE(IF($E$642:$E$648=E637,ROW($E$642:$E$648)-ROW($E$642)+1),IF(E638="Last",1,COUNTIF($E$642:$E$648,E637))))
Ctrl+Shift+Enter

Hope this helps

M.

 

[JamesonMH]

Maybe...

=INDEX($F$642:$F$648,LARGE(IF($E$642:$E$648=E637,ROW($E$642:$E$648)-ROW($E$642)+1),IF(E638="Last",1,COUNTIF($E$642:$E$648,E637))))
Ctrl+Shift+Enter

Hope this helps

M.

That absolutely does. Now the formula's half the size. Much cleaner to audit and more efficient. Thanks for your help Marcelo!

James

 

[Marcelo Branco]

You are welcome. Glad to help.

M.