SMALL Function across multiple date ranges

[Kingsof82]

Hi,

I'm looking to find the second lowest date entered in ranges from multiple worksheets. I can return the second lowest from each range individually, e.g.

=SMALL(Observation!I16:BF16,2)

However, attempting to pull the second lowest from all ranges using the following returns a value error:

=SMALL((Probation!J13:U13, Appraisal!J13:O13,Supervision!I13:CB13,Observation!I13:BF13),2)

Any help is greatly appreciated, thanks

 

[DRSteele]

Do you only have Excel 2016? If you have access to 365, this formula would be easy.

 

[Phuoc]

Try this:

Book1
	A	B	C	D	E	F	G
1	3		9
2			2
3							3
4
5			10		5		8
6
7
Sheet2

Cell Formulas
Range	Formula
A1	A1	=SMALL((C1:C7,E1:E7,G1:G7),2)

 

[Peter_SSs]

Try this:

Did you miss this part of the question perhaps?

.. ranges from multiple worksheets

 

[Phuoc]

Excel version has HSTACK function, can use:

=SMALL(HSTACK(Probation!J13:U13, Appraisal!J13:O13,Supervision!I13:CB13,Observation!I13:BF13),2)

 

[Tetra201]

See if the following formula works for you:

=AGGREGATE(15,6,CHOOSE({1;2;3;4},Probation!J13:U13,Appraisal!J13:O13,Supervision!I13:CB13,Observation!I13:BF13),2)

 

[Peter_SSs]

Excel version has HSTACK function

Your Excel version might, but the OP's does not (if their profile information is correct).

 

[Kingsof82]

Thanks for the input, yes, unfortunately I'm still running on Office 2016 in my workplace.

=AGGREGATE(15,6,CHOOSE({1;2;3;4},Probation!J13:U13,Appraisal!J13:O13,Supervision!I13:CB13,Observation!I13:BF13),2)[/CODE]

Unfortunately the above is returning the value of "00/01/1900", presumably because my ranges contain empty cells.

Thanks

 

[Peter_SSs]

presumably because my ranges contain empty cells.

In that case, ty this modification

=AGGREGATE(15,6,CHOOSE({1;2;3;4},--(Probation!J13:U13&""),--(Appraisal!J13:O13&""),--(Supervision!I13:CB13&""),--(Observation!I13:BF13&"")),2)

 

[Kingsof82]

Thanks,

Unfortunately this is now returning a number error