Largest Value based on Multiple criteria

[A10hammond]

Hi there,

I'm working with a data sheet and am trying to get the Nth largest value in a specific column based on multiple criteria. My single criteria formula works fine, this is it:
={LARGE(IF('Sheet1'!C:C=3,ABS('Sheet1'!W:W),""),1)}

However, I run into the #VALUE! error once I add another argument, not sure if using AND() is correct, and my version of excel doesn't have the IFS() function:
={LARGE(IF(AND('Sheet1'!C:C=3,'Sheet1'!E:E="Bank"),ABS('Sheet1'!W:W),""),1)}

Is there an error in my syntax or a correct way to do this?

Working on Windows with Excel 2016.

 

[jasonb75]

Try

=AGGREGATE(14,6,ABS('Sheet1'!W:W)/('Sheet1'!C:C=3)/('Sheet1'!E:E="Bank"),1)

As with anything, it would be preferable to use limited ranges instead of full columns.