Using Large function based on Multiple Criteria

Dellboy

New Member
Joined
Sep 16, 2020
Messages
33
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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
You can use whole columns, but the formula is then looking at over 3million cells, which can slow your workbook down considerably.
 
Upvote 0
With you, will reduce the number. Once again, thank you for your help, wish I came here sooner!
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
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.
 
Upvote 0
:unsure: Why do you want to find the name, when you are filtering the data by that name?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,932
Messages
6,110,748
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top