Using Large function based on Multiple Criteria

Dellboy

New Member
Joined
Sep 16, 2020
Messages
17
Office Version
  1. 365
Hello,

I can't seem to get a Large formula to work based on two criteria's.

I can easily get a oldest value based one value, but I'm struggling to add another. See table below;

The 227 figure is based on =LARGE(IF(B:B="Jim",D:D),1) - so it returns the oldest days old for Jim.

However, when I want to the reference number as another search criteria, I get the #NUM error and have no idea why. Formula is as follows - =LARGE(IF(B:B="Jim",IF(C:C="2020",D:D)),1).

Basically, I want to create a top 5 but have no idea why that formula does not work. Any help would be greatly appreciated.

NameReferenceDays old227Jim Days Oldest
Jim2020114#NUM!Jim and 2020 Days Oldest
Jim2021120
Jim2021227
Sam202151
Sam202047
Jim202077
Steve2021290
Steve2022198
Harry202084
Jim202179
Steve202077
Harry202257
Sam202154
Sam202041
Steve202041
Jim202140
Jim202032
Harry202223
Steve202173
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,303
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
You need to remove the quotes from the year.
Excel Formula:
=LARGE(IF(B:B="Jim",IF(C:C=2020,D:D)),1)
Also you should not use whole column references in an array formula.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,303
Office Version
  1. 365
Platform
  1. Windows
As you have 365 you could also use this to get the top 5
+Fluff New.xlsm
ABCDEFGH
1NameReferenceDays oldJim Days Oldest
2Jim2020114227Jim and 2020 Days Oldest
3Jim2020120
4Jim2020227
5Sam202151227
6Sam202047120
7Jim202077114
8Steve202129079
9Steve202219877
10Harry202084
11Jim202079
12Steve202077
13Harry202257
14Sam202154
15Sam202041
16Steve202041
17Jim202140
18Jim202032
19Harry202223
20Steve202173
21
Master
Cell Formulas
RangeFormula
F2F2=LARGE(IF(B:B="Jim",IF(C:C=2020,D:D)),1)
F5:F9F5=INDEX(SORT(FILTER(D2:D20,(B2:B20="Jim")*(C2:C20=2020)),,-1),ROW(1:5))
Dynamic array formulas.
 

Dellboy

New Member
Joined
Sep 16, 2020
Messages
17
Office Version
  1. 365
Works, Thank you so much.

Out of interest, why can't I use a whole column reference in an array formula? The reason I ask is that I want the sheet to be dynamic, so if I add more lines of information, it automatically provides my with the oldest days old by the formula.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,303
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You can use whole columns, but the formula is then looking at over 3million cells, which can slow your workbook down considerably.
 

Dellboy

New Member
Joined
Sep 16, 2020
Messages
17
Office Version
  1. 365
With you, will reduce the number. Once again, thank you for your help, wish I came here sooner!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,303
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You're welcome & thanks for the feedback.
 

Dellboy

New Member
Joined
Sep 16, 2020
Messages
17
Office Version
  1. 365
Have one other query - how automatically assign the name based on the formula output - .e.g in column G5, there is a formula that looks up the answer in F5 (227) which then matches the name to the figure - Jim in this instance.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,303
Office Version
  1. 365
Platform
  1. Windows
:unsure: Why do you want to find the name, when you are filtering the data by that name?
 

Dellboy

New Member
Joined
Sep 16, 2020
Messages
17
Office Version
  1. 365
Apologies, probably wrongly worded.

I want the cell to look up another value. For example, if the E column was populated with text (E.g. Say E4 was populated with "Higher"). How would I do it that cell G5 returns that text based on the answer from 227. I think its something to do with Vlookup, but I cant get it to work.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,979
Messages
5,526,024
Members
409,677
Latest member
ssh99

This Week's Hot Topics

Top