Today I learned how to sort numbers from multiple cell ranges thanks to Sam Miller. It is surprisingly simple and easy.
Formula in cell H3:
The SMALL function ignores text and blank cells, however, not error values.
The first argument in the SMALL function is the array parameter: SMALL(array, k).
Use parentheses to enable multiple cell ranges in the first argument.
($B$3:$B$8, $D$3:$D$6, $F$3:$F$7)
The , (comma) separates the cell references.
The second argument allows you to specify which number to extract based on their sort order.
The ROWS function allows you to insert new rows and columns in your worksheet without breaking the formula.
The cell reference contains two parts, one is an absolute cell reference and the other is a relative cell reference.
The $ sign allows you to specify an absolute cell reference, this cell reference does not change when you copy the formula to cells below.
SMALL function with multiple cell ranges.xlsx
SMALL function – multiple criteria
How to use the PI function
Create a list of duplicates where adjacent cell value meets a condition
How to use the RADIANS function
Extract cell references from all cell ranges populated with values in a sheet
Filter values in common between two cell ranges [UDF]
5 easy ways to VLOOKUP and return multiple values
Match two criteria and return multiple records
Sort a column alphabetically
Find closest value
How to use the SMALL function
Extract largest values from two columns
How to ignore zeros using the SMALL function
SMALL function – INDEX MATCH
INDEX MATCH – multiple results
Leave a Comment
How to add a formula to your comment:
Remember to convert less than and larger than signs to html character entities before you post your comment.
How to add VBA code to your comment:
[vb 1="vbnet" language=","]
How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.
You can contact me through this webpage
Mail (will not be published) (required)
Notify me of followup comments via e-mail
User Defined Functions
Advanced Excel Course
Posts in category