Extract largest values from two columns using array formula in excel
Question: How do I extract the five largest values from two or more columns?
Answer:
Formula in A12:
Formula in B12:
How to create an array formula
- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.
Named ranges
tbl (A2:D6)
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
There is a similar formula and explanation on this page:
Unique distinct values from multiple columns using array formula
Download excel example file.
extract-largest-values-from-two-columns
(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:
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
Recommended categories
Hi - Thank you for putting this example on line...my question is, how do you change the formula to select the top 5 highest (loses) negitive values first.
Thank you.
Hey -- would you know how to alter this formula so it can find the largest values from your data set but with 3, 4 or 5 columns... i essentially want to scale what you have but for more data
Chris,
Almost two years later...maybe someone else can benefit from my answer.
Array formula in cell A12:
Array formula in cell B12:
Matt,
I think the new formulas work for a larger range now.
Hi There
I could not make it work for a range of 4 columns and looking for Top and bottom 5 from the 5th column. My data appears like a classification, sub classification, category, class and value; from Column A to E. It consists about 12000 rows and I am trying display top 5 and bottom 5 values in a separate table with all information in those top / bottom rows (e.g the output is Class-2, subclass-a, category-x, class-z and value- 863)
Thanks
Gill,
See attached file:
Gill.xlsx
I don´t think these formulas work if there are duplicate records.
[...] The totals from daily activty is on Row 4 for about 50 companies names listed on Row 5 The graph from this only wants to: a. Show the top 10 companies based on the associated totals b. Alwasys include Our company in the top 10. Static: Row 4 Column D to Az - List of totals for each column Row 5 Column D to Az - List of compananies (in Alphabet order) Our company in middle Grows Daily - daily sales numbers beow: The row 4 Column Total is based on rows below it that are added with daily sales. the rows below start 1/2/2013 --> to current date Each date's row shows total sales for each company as it is published daily. Each day, after the day's sales are entered, a chart needs to be created showing the top 10 companies. But, Our company always needs to be included in the top 10 weather it made the top 10 or not. Alternative: If Our company can't always be included in the top 10, then perhaps a trend lind across the chart showing Our company - this would show what companies are above and below Our company. A Formula or VBA Code would be acceptable. Any links to examples for charting with code or an update would be great. Somewhat related is this article: Extract largest values from two columns using array formula in excel | Get Digital Help - Microsoft ... [...]