## 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

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]### 8 Responses to “Extract largest values from two columns using array formula in excel”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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 ... [...]

Hi MR. Oscar

Thanks for your answers. I need a guidance. I want to be creative in excel but in the array formulas I can't, because I don't have enough information about them.

Could you please introduce related sources? and please tell me suggestions for improving my skills in Excel.

Thanks

Hi

I need to look at two columns in two different tables for values and then find the top 20 values. Then find the corresponding names in these two different tables as well. could you plz help. I tried to use Gill's sheet.