Finding Largest Value based on Criteria

[gldurand]

Hi Folks,

I have this formula that is returning #N/A. Can someone assist

=LARGE(IF('Source Data'!$BE$2:$BE$65000="VALUE A",
IF('Source Data'!$S$2:$S$65000="VALUE B",
ISNUMBER(MATCH('Source Data'!$H$1:$H$65000,{"VALUE C","VALUE D"},0)),
'Source Data'!$BK$2:$BK$65000)),1)

Thanks

 

[jvanbonn]

By no means am I an expert, but...

...
=LARGE(IF('Source Data'!$BE$2:$BE$65000="VALUE A",
IF('Source Data'!$S$2:$S$65000="VALUE B",
...

I suspect these are the statements that are giving you the problem:
'Source Data'!$BE$2:$BE$65000="VALUE A"
'Source Data'!$S$2:$S$65000="VALUE B"

I don't think the "IF" a Range of cells is equal to a single value is ever going to produce TRUE. Maybe someone with more experience can chime in.

 

[violet_universe]

=LARGE(IF('Source Data'!$BE$2:$BE$65000="VALUE A",IF('Source Data'!$S$2:$S$65000="VALUE B",ISNUMBER(MATCH('Source Data'!$H$1:$H$65000,{"VALUE C","VALUE D"},0)),'Source Data'!$BK$2:$BK$65000)),1)

you only get three results :false ,ture or the data in $BK$2:$BK$65000

 

[Scott Huish]

Yes, you can get TRUE, it's called an array formula and must be confirmed with CTRL-SHIFT-ENTER rather than just Enter. The Large function will ignore the FALSE results in the array.

Although in this case you can use MAX instead of LARGE(x,1)

 

[gldurand]

Hi HOTPEPPER

Can you please list the entire formula, i tried to make it work with MAX, but somehow I am still getting the #N/A.

I want to get the largest value in column BK based on 2 criteria in seperate columns (BE & S) and 2 values of column H

=LARGE(

IF('Source Data'!$BE$2:$BE$65000="VALUE A",
IF('Source Data'!$S$2:$S$65000="VALUE B",
ISNUMBER(MATCH('Source Data'!$H$1:$H$65000,{"VALUE C","VALUE D"},0)),
'Source Data'!$BK$2:$BK$65000)),1)

What would it be using MAX

Thanks for your assistance