LARGE IF OR question

jcooooper

Board Regular
Joined
Mar 24, 2018
Messages
52
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!
 

Some videos you may like

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
Joined
Aug 23, 2010
Messages
16,393
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
Joined
Mar 24, 2018
Messages
52
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
Joined
Aug 23, 2010
Messages
16,393
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
Joined
May 26, 2009
Messages
17,217
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Aug 23, 2010
Messages
16,393
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
Joined
May 26, 2009
Messages
17,217
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Aug 23, 2010
Messages
16,393
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
Joined
Aug 23, 2010
Messages
16,393
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
Joined
May 26, 2009
Messages
17,217
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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! ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,258
Messages
5,527,663
Members
409,781
Latest member
Maxcwy2020

This Week's Hot Topics

Top