LARGE IF OR question

[jcooooper]

Hello,

Currently I have the following formula which works great:

{=LARGE(IF($Z$1:$Z$10000="EQUITY",$AA$1:$AA$10000),J23)}

If I then wanted to include other candidates for large, with different text values than "EQUITY", all in column Z, how would I expand the above to include these?.

Been fiddling around with it but no dice.

Thanks!

 

[Marcelo Branco]

Maybe something like this

=LARGE(IF(ISNUMBER(MATCH($Z$1:$Z$10000,MyList,0)),$AA$1:$AA$10000),J23)
Ctrl+Shift+Enter

where MyList is a named range that contains the values of interest

M.

 

[jcooooper]

Maybe something like this

=LARGE(IF(ISNUMBER(MATCH($Z$1:$Z$10000,MyList,0)),$AA$1:$AA$10000),J23)
Ctrl+Shift+Enter

where MyList is a named range that contains the values of interest

M.

Thanks Marcelo, can't seem to get it to work for me. Is it possible because my named ranges are text items rather than values?

If it makes any difference, the other text values are ABS, BND, CMBS

 

[Marcelo Branco]

Worked for me

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

X​

[/TD]
[TD="bgcolor: #DCE6F1"]

Y​

[/TD]
[TD="bgcolor: #DCE6F1"]

Z​

[/TD]
[TD="bgcolor: #DCE6F1"]

AA​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

MyList​

[/TD]
[TD]

Result​

[/TD]
[TD]

ABS​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

EQUITY​

[/TD]
[TD]

20​

[/TD]
[TD]

XXX​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

ABS​

[/TD]
[TD][/TD]
[TD]

EQUITY​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

BND​

[/TD]
[TD][/TD]
[TD]

EQUITY​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

CMBS​

[/TD]
[TD][/TD]
[TD]

ZZZ​

[/TD]
[TD]

50​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

BND​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #FFFF00"]

BND​

[/TD]
[TD="bgcolor: #FFFF00"]

20​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

CMBS​

[/TD]
[TD]

12​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

zzz​

[/TD]
[TD]

40​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

CMBS​

[/TD]
[TD]

10​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


MyList --> X2:X5
J23 = 1 (to get the largest)

Array formula in Y2
=LARGE(IF(ISNUMBER(MATCH($Z$1:$Z$10000,MyList,0)),$AA$1:$AA$10000),J23)
Ctrl+Shift+Enter

M.

 

[JoeMo]

Marcelo's formula looks ok to me, but just in case, this alternative array formula (ctrl+shift+enter) works for me with one additional text value besides "EQUITY".

=LARGE(IF(OR($Z$1:$Z$10000="EQUITY",$Z$1:$Z$10000="JOE"),$AA$1:$AA$10000),J23)

 

[Marcelo Branco]

Hi JoeMo

I think you meant
=LARGE(IF(($Z$1:$Z$10000="EQUITY")+($Z$1:$Z$10000="JOE"),$AA$1:$AA$10000),J23)

It's an array formula, so is necessary to add the conditions to evaluate properly an OR condition

M.

 

[JoeMo]

Hi JoeMo

I think you meant
=LARGE(IF(($Z$1:$Z$10000="EQUITY")+($Z$1:$Z$10000="JOE"),$AA$1:$AA$10000),J23)

It's an array formula, so is necessary to add the conditions to evaluate properly an OR condition

M.

Hi Marcelo,
Strangely,using the + operator to signify an OR was the first thing I tried, and couldn't get it to work. Even stranger - the formula I posted works for me. If I enter either "EQUITY" or "JOE" anywhere in the Z range, the formula returns the nth largest value in the AA range where n is the number in J23.

 

[Marcelo Branco]

Hi Marcelo,
Strangely,using the + operator to signify an OR was the first thing I tried, and couldn't get it to work. Even stranger - the formula I posted works for me. If I enter either "EQUITY" or "JOE" anywhere in the Z range, the formula returns the nth largest value in the AA range where n is the number in J23.

J23 =1
Using the data in post 4, see what happens by entering this formula
=LARGE(IF(OR($Z$1:$Z$10000="EQUITY",$Z$1:$Z$10000="BND"),$AA$1:$AA$10000),J23)
Ctrl+Shift+Enter

For me it returned 50 (???) - that is, since one single value satisfies the OR all the rows are evaluated as TRUE (???)

M.

 

[Marcelo Branco]

On the other hand, this formula worked for me (returned 20)
=LARGE(IF(($Z$1:$Z$10000="EQUITY")+($Z$1:$Z$10000="BND"),$AA$1:$AA$10000),J23)
Ctrl+Shift+Enter

where J23 = 1

M.

 

[JoeMo]

J23 =1
Using the data in post 4, see what happens by entering this formula
=LARGE(IF(OR($Z$1:$Z$10000="EQUITY",$Z$1:$Z$10000="BND"),$AA$1:$AA$10000),J23)
Ctrl+Shift+Enter

For me it returned 50 (???) - that is, since one single value satisfies the OR all the rows are evaluated as TRUE (???)

M.

Yes, I think we are looking for two different outputs. The OR finds the nth-largest value in col AA range if there is any instance of "EQUITY" OR "BND" in col Z range. This:
=LARGE(IF(OR($Z$1:$Z$10000="EQUITY",$Z$1:$Z$10000="BND"),$AA$1:$AA$10000),J23)
looks for the nth- largest value that aligns with either Text value. I see now what threw me off in using the + operator initially is that the formula returms #NUM ! if there are fewer than n values that line up with the Text values. Sorry about that!