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]

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.

Ah, now i understand what you did. I think we should wait that OP clarifies what he needs.

M.

 

[jcooooper]

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.

This works great, thanks so much!