Using Large function based on Multiple Criteria

[Dellboy]

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

 

[Dellboy]

Ok, how about

=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})

hmm, still returns the 360 value. Its close I'm sure, im looking to return the value from column B (name) and C (Type). So it matches to the days old 360 - but also returns the name and reference from that row so it matches.

 

[Dellboy]

Top 5 Days old	Name	Type
360	Jim	2789

So would expect the values above in the table on sheet 1

 

[Fluff]

Forgot to put in the sort column

+Fluff New.xlsm
	A	B	C
1
2	360	Jim	2789
3	330	Harry	5526
4	329	Jim	2541
5	329	Jim	1234
6	315	Jim	5412
7
Sheet1

Cell Formulas
Range	Formula
A2:C6	A2	=INDEX(SORT(FILTER(Sheet2!$B$3:$F$30,(Sheet2!$D$3:$D$30="Apple")*(Sheet2!$E$3:$E$30=2021)),5,-1,),ROW(1:5),{5,1,2})
Dynamic array formulas.

 

[Dellboy]

You sir are a godsend. Works. Thank you so much for this.

One final thing, how do you return blanks in a cell if you get a #ref or #calc? I normally enter - iferror "" - but not sure when to put that in the formula

 

[Fluff]

You could use

=IFERROR(INDEX(SORT(FILTER(Sheet1!$B$3:$F$30,(Sheet1!$D$3:$D$30="Apple")*(Sheet1!$E$3:$E$30=2029)),,-1),ROW(1:5),{5,1,2}),"")

but it will give you a spill range showing ""

 

[Dellboy]

Once again thank you so much

 

[Fluff]

You're welcome.