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

Answer:

Formula in A12:

=IF(MAX(IF(COUNTIF(B11:$B$11, Price2)*COUNTIF(A11:$A$11, List2)>=1, -1E+307, Price2))>MAX(IF(COUNTIF(B11:$B$11, Price1)*COUNTIF(A11:$A$11, List1)>=1, -1E+307, Price1)), INDEX(List2, MATCH(MAX(IF(COUNTIF(B11:$B$11, Price2)*COUNTIF(A11:$A$11, List2)>=1, -1E+307, Price2)), IF(COUNTIF(B11:$B$11, Price2)*COUNTIF(A11:$A$11, List2)>=1, -1E+307, Price2), 0)), INDEX(List1, MATCH(MAX(IF(COUNTIF(B11:$B$11, Price1)*COUNTIF(A11:$A$11, List1)>=1, -1E+307, Price1)), IF(COUNTIF(B11:$B$11, Price1)*COUNTIF(A11:$A$11, List1)>=1, -1E+307, Price1), 0))) + CTRL + SHIFT + ENTER copied down as far as necessary

Formula in B12:

=IF(MAX(IF(COUNTIF(B11:$B$11, Price2)*COUNTIF(A11:$A$11, List2)>=1, -1E+307, Price2))>MAX(IF(COUNTIF(B11:$B$11, Price1)*COUNTIF(A11:$A$11, List1)>=1, -1E+307, Price1)), INDEX(Price2, MATCH(MAX(IF(COUNTIF(B11:$B$11, Price2)*COUNTIF(A11:$A$11, List2)>=1, -1E+307, Price2)), IF(COUNTIF(B11:$B$11, Price2)*COUNTIF(A11:$A$11, List2)>=1, -1E+307, Price2), 0)), INDEX(Price1, MATCH(MAX(IF(COUNTIF(B11:$B$11, Price1)*COUNTIF(A11:$A$11, List1)>=1, -1E+307, Price1)), IF(COUNTIF(B11:$B$11, Price1)*COUNTIF(A11:$A$11, List1)>=1, -1E+307, Price1), 0))) + CTRL + SHIFT + ENTER copied down as far as necessary

Named ranges
List1 (A2:A6)
List2 (C2:C6)
Price1 (B2:B6)
Price2 (D2:D6)
What is named ranges?

How to implement array formula to your workbook
Change named ranges. If your list starts at, for example, cell F3 and G3. Change A11:$A$11 in the above formula to F2:$F$2 and change B11:$B$11 to G2:$G$2

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

Functions in this article

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:

1. Identify largest text value in a column using array formula in excel
2. Count matching cell values in two columns in excel
3. Extract unique values from a range using array formula in excel
4. Merge two columns with possible blank cells in excel (formula)
5. Filter unique values from a range using array formula in excel
6. Filter text values existing in range 1 but not in range 2 using array formula in excel