Question: How do I extract the five largest values from two or more columns?

Formula in A12:

=INDEX(tbl, SMALL(IF(LARGE(tbl,ROW(A1))=tbl, ROW(tbl)-MIN(ROW(tbl))+1),COUNTIF(\$B\$12:B12,B12)), MATCH(LARGE(tbl,ROW(A1)), INDEX(tbl, SMALL(IF(LARGE(tbl,ROW(A1))=tbl, ROW(tbl)-MIN(ROW(tbl))+1),COUNTIF(\$B\$12:B12,B12)), , 1), 0)-1, 1)

Formula in B12:

=INDEX(tbl; MIN(IF(LARGE(tbl;ROW(A1))=tbl; ROW(tbl)-MIN(ROW(tbl))+1)); MATCH(LARGE(tbl;ROW(A1)); INDEX(tbl; MIN(IF(LARGE(tbl;ROW(A1))=tbl; ROW(tbl)-MIN(ROW(tbl))+1)); ; 1); 0); 1)

### How to create an array formula

1. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
2. Press and hold Ctrl + Shift.
3. Press Enter once.
4. Release all keys.

### Named ranges

tbl (A2:D6)

What is named ranges?

### Use array formula in your excel sheet

Change cell range in the named range. Array formula in cell A12, adjust COUNTIF(\$B\$12:B12,B12) to your sheet.

### Explaining array formula

Unique distinct values from multiple columns using array formula

extract-largest-values-from-two-columns
(Excel 97-2003 Workbook *.xls)

IF(logical_test,[value_if:true],[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

ROW(reference)
returns the row number of a reference

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

MAX(number1,[number2],)
Returns the largest value in a set of values. Ignores logical values and text.

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value

### Related posts:

Identify largest text value in a column using array formula in excel

Unique distinct values from multiple columns using array formula

Extract a unique distinct list from two columns using excel 2007 array formula

Extract a list of duplicates from two columns combined using array formula in excel

Extract a list of duplicates from three columns combined using array formula in excel