## Extract largest values from two columns

*Article last updated on January 24, 2018*

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

5 easy ways to extract unique distinct values

You have quite a few options to choose from if you are looking for a way to create a unique […]

How to create a list of random unique numbers

Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

Find latest date based on a condition

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]

How to create a list of random unique numbers

Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and […]

Extract unique distinct values from a multi-column cell range

Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]

Calculate average of last 10 data with possible blank cells

Question: Answer: This array formula creates a dynamic range, filtering the 10 last data. Adjust cell ranges $A$1:$A$25 in formula below. […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]

Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]

### 8 Responses to “Extract largest values from two columns”

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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.