# Using Large function based on Multiple Criteria

#### Dellboy

##### New Member
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.

 Name Reference Days old 227 Jim Days Oldest Jim 2020 114 #NUM! Jim and 2020 Days Oldest Jim 2021 120 Jim 2021 227 Sam 2021 51 Sam 2020 47 Jim 2020 77 Steve 2021 290 Steve 2022 198 Harry 2020 84 Jim 2021 79 Steve 2020 77 Harry 2022 57 Sam 2021 54 Sam 2020 41 Steve 2020 41 Jim 2021 40 Jim 2020 32 Harry 2022 23 Steve 2021 73

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

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
With you, will reduce the number. Once again, thank you for your help, wish I came here sooner!

#### Fluff

##### MrExcel MVP, Moderator

You're welcome & thanks for the feedback.

#### Dellboy

##### New Member
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
Why do you want to find the name, when you are filtering the data by that name?

#### Dellboy

##### New Member
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.

Replies
11
Views
175
Replies
3
Views
236
Replies
5
Views
131
Replies
5
Views
400
Replies
1
Views
2K