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
 
How about
+Fluff New.xlsm
ABCDEFG
1NameReferenceDays old
2Jim2020120A
3Jim2020120B
4Jim2020227CJim
5Sam202151D227C
6Sam202047E120A
7Jim202077F120B
8Steve2021290G79J
9Steve2022198H77F
10Harry202084I
11Jim202079J
12Steve202077K
13Harry202257L
14Sam202154M
15Sam202041N
16Steve202041O
17Jim202140P
18Jim202032Q
19Harry202223R
20Steve202173S
21
Main
Cell Formulas
RangeFormula
F5:F9F5=INDEX(SORT(FILTER(D2:D20,(B2:B20=F4)*(C2:C20=2020)),,-1),ROW(1:5))
G5:G9G5=INDEX(FILTER($E$2:$E$20,($D$2:$D$20=F5)*($B$2:$B$20=F$4)),COUNTIF(F$5:F5,F5))
Dynamic array formulas.
 
Upvote 0

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

Fluff, amazing formulas!
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0
Hope you can also help with this, sort of an add on from the formula below

=INDEX(SORT(FILTER(D2:D20,(B2:B20=F4)*(C2:C20=2020)),,-1),ROW(1:5))

So this would return a value from D (days old). Is there any way to return the value from column B (e.g. Jim) as the result of the sorting from the days old (column D)? Basically, I want a second column to match the answer for days old that corresponds to the name.
 
Upvote 0
im sure its something to do with vlookup, or xlookup. Just want to match the cell based on the outcome of the number of days (so if the answer is on D3, it looks up the value of C3)
 
Upvote 0
actually, I dont think that would work as I may have values that are the same (e.g. same number of days). I think it needs to be an extension of the index formula above
 
Upvote 0
I'm afraid I don't understand what you are asking for.
If you are sorting the days old for Jim, why would you want to then return that name next to each number?
 
Upvote 0
Cant seem to attached a sheet for reivew.

Say the below is the data I have;

NameTypeFruitReferenceDays oldDate openedToday
Jim2789Apple202136006/05/201918/09/2020
Steve2888Apple202011018/04/202018/09/2020
Steve2456Banana202030718/07/201918/09/2020
Harry2331Orange20218820/05/202018/09/2020
Jim2541Apple202132918/06/201918/09/2020
Steve9871Orange202032918/06/201918/09/2020
Chris2355Apple202016501/02/202018/09/2020
Jim9871Apple202029505/08/201918/09/2020
Jim2333Banana20201104/09/202018/09/2020
Harry2545Orange202113219/03/202018/09/2020
Chris8546Banana202135414/05/201918/09/2020
Steve9541Orange202033015/06/201918/09/2020
Chris2287Apple202128517/08/201918/09/2020
Steve3655Banana2020415/09/202018/09/2020
Steve4321Orange202031014/07/201918/09/2020
Jim1234Apple202132918/06/201918/09/2020
Harry5526Apple202133015/06/201918/09/2020
Harry2896Orange20218820/05/202018/09/2020
Steve2784Orange202027009/09/201918/09/2020
Chris9654Banana202135907/05/201918/09/2020
Hary1255Banana202033015/06/201918/09/2020
Jim5412Apple202131507/07/201918/09/2020
Chris5522Apple202128420/08/201918/09/2020
Steve7546Orange202136006/05/201918/09/2020
Steve2298Orange202137416/04/201918/09/2020
Jim2844Banana202139122/03/201918/09/2020
Jim2471Banana202034527/05/201918/09/2020
Chris2546Apple202013219/03/202018/09/2020


Thats from sheet 2.

I then have a front sheet which is a summary of the top 5 days old based on "Apple" (column C) and "2021" (column D).

use the formula (you gave me earlier) - =INDEX(SORT(FILTER(Sheet2!$F$3:$F$30,(Sheet2!$D$3:$D$30="Apple")*(Sheet2!$E$3:$E$30=2021)),,-1),ROW(1:5))

This then gives me the top 5, so I have the following table result on sheet 1;

Top 5 Days oldNameType
360​
HEREHERE
330​
329​
329​
315​

I would then like to know the name and type based on the value from the days old column. not sure if its a simple vlookup, but I cant seem to get that to work.

Hopefully that makes sense
 
Upvote 0
Ok, how about
Excel Formula:
=INDEX(SORT(FILTER(Sheet2!$B$3:$F$30,(Sheet2!$D$3:$D$30="Apple")*(Sheet2!$E$3:$E$30=2021)),,-1),ROW(1:5),{5,1,2})
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

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