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. How to filter values between 0.5 and 1.5 from two columns in excel 2007
2. Comparing two columns and sum unique values using array formula in excel
3. Extract a list of duplicates from two columns combined using array formula in excel
4. Extract a list of duplicates from three columns combined using array formula in excel
5. Extract a unique distinct list from two columns using excel 2007 array formula
6. Filter duplicates from two columns combined and sort from A to Z using array formula in excel
7. Identify largest text value in a column using array formula in excel
8. Sort text cells alphabetically from two columns using excel array formula
9. Identify missing values in two columns using excel formula
10. Filter common values from three columns in excel