# LARGE IF OR question

#### jcooooper

##### Board Regular
Hello,

Currently I have the following formula which works great:

Code:
``{=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!

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### Marcelo Branco

##### MrExcel MVP
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

##### Board Regular
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

##### MrExcel MVP
Worked for me

 X​ Y​ Z​ AA​ 1​ MyList​ Result​ ABS​ 2​ 2​ EQUITY​ 20​ XXX​ 3​ 3​ ABS​ EQUITY​ 2​ 4​ BND​ EQUITY​ 5​ 5​ CMBS​ ZZZ​ 50​ 6​ BND​ 4​ 7​ BND​ 20​ 8​ CMBS​ 12​ 9​ zzz​ 40​ 10​ CMBS​ 10​ 11​

<tbody>
</tbody>

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

##### MrExcel MVP

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".
Code:
``=LARGE(IF(OR(\$Z\$1:\$Z\$10000="EQUITY",\$Z\$1:\$Z\$10000="JOE"),\$AA\$1:\$AA\$10000),J23)``

#### Marcelo Branco

##### MrExcel MVP
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

##### MrExcel MVP

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

##### MrExcel MVP
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.

Last edited:

#### Marcelo Branco

##### MrExcel MVP
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

##### MrExcel MVP
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!

Replies
8
Views
131
Replies
2
Views
72
Replies
19
Views
194
Replies
1
Views
48
Replies
11
Views
121