Need help simplifying array, max formula

[ibesmond]

I built a formula to retrieve a date from a list.

{MAX(IF(AM2='POs Recpt Date '!A:A,'POs Recpt Date '!E:E)}

[TABLE="width: 352"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD] PO Value [/TD]
[TD]DC [/TD]
[TD]PO[/TD]
[TD]Vendor [/TD]
[TD]PO Date recvd[/TD]
[/TR]
[TR]
[TD]$18,022.20[/TD]
[TD]LIV[/TD]
[TD="align: right"]217550[/TD]
[TD] XXX[/TD]
[TD="align: right"]1/0/1900[/TD]
[/TR]
[TR]
[TD]$19,050.00[/TD]
[TD]LIV[/TD]
[TD="align: right"]217603[/TD]
[TD] XXX[/TD]
[TD="align: right"]8/7/2015[/TD]
[/TR]
[TR]
[TD]$19,050.00[/TD]
[TD]LIV[/TD]
[TD="align: right"]217603[/TD]
[TD] XXX[/TD]
[TD="align: right"]8/7/2015[/TD]
[/TR]
[TR]
[TD]$18,022.20[/TD]
[TD]LIV[/TD]
[TD="align: right"]217555[/TD]
[TD] XXX[/TD]
[TD="align: right"]8/12/2015[/TD]
[/TR]
</tbody>[/TABLE]

When the AM2 value is not listed in Sheet 2, it returns a value of 0 (or in this case since it is a formula a value of 1/0/1900, so I modified the formula to return a blank...

=IF(MAX(IF(AM2='POs Recpt Date '!A:A,'POs Recpt Date '!E:E))=0,"",MAX(IF(AM2='POs Recpt Date '!A:A,'POs Recpt Date '!E:E)))

[TABLE="width: 352"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD] PO Value [/TD]
[TD]DC [/TD]
[TD]PO[/TD]
[TD]Vendor [/TD]
[TD]PO Date recvd[/TD]
[/TR]
[TR]
[TD]$18,022.20[/TD]
[TD]LIV[/TD]
[TD="align: right"]217550[/TD]
[TD] XXX[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]$19,050.00[/TD]
[TD]LIV[/TD]
[TD="align: right"]217603[/TD]
[TD] XXX[/TD]
[TD="align: right"]8/7/2015[/TD]
[/TR]
[TR]
[TD]$19,050.00[/TD]
[TD]LIV[/TD]
[TD="align: right"]217603[/TD]
[TD] XXX[/TD]
[TD="align: right"]8/7/2015[/TD]
[/TR]
[TR]
[TD]$18,022.20[/TD]
[TD]LIV[/TD]
[TD="align: right"]217555[/TD]
[TD] XXX[/TD]
[TD="align: right"]8/12/2015[/TD]
[/TR]
</tbody>[/TABLE]

...but it takes a very long time to process the formula because I have at least 1000 rows in one table and almost 130,000 rows in another table.

I was wondering if anyone had any ideas to shorten the formula. so that it doesn't have to calculate the max twice.

 

[LalSoHard]

Why can't you just use vlookup with an iferror around it?

 

[Aladin Akyurek]

Try to avoid whole column references in this type of formulas. Control+shift+enter, not just enter:

Either...

=IF(COUNTIFS('POs Recpt Date '!$A$2:$A$400,AM2),MAX(IF(AM2='POs Recpt Date '!$A$2:$A$400,'POs Recpt Date '!$E$2:$E$400)),"")

Or...

=IFERROR(1/(1/MAX(IF(AM2='POs Recpt Date '!$A$2:$A$400,'POs Recpt Date '!$E$2:$E$400))),"")