## Match two columns

*Article last updated on August 21, 2018*

The array formula in cell D12 matches two values in two columns each and returns a value on the same row.

If you are looking for a way to compare two columns for differences or compare two columns for same values, please click links.

You are not limited to formulas, conditional formatting allows you to compare two columns and highlight matches or compare two columns and highlight differences.

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula enclosed with curly brackets telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

The COUNTIFS function counts rows where both values match, however, it returns an array that corresponds to the number of rows in cell range B3:D9.

returns {0;0;0;0;1;0;0} , shown in column F in picture below.

Both values match in row 7.

The MATCH function identifies the relative position of the matching values.

and returns 5, the match is the fifth value in the array.

The INDEX function returns the corresponding value in column D.

and returns BB in cell D12.

### Alternative regular formula

The above formula is a regular formula, it is slightly larger than the first formula at the beginning of this post.

### Download excel *.xlsx

### Compare two columns for differences

This article extracts values that only exists in one column:

Compare two columns and show differences

Array Formula in B11: =INDEX($B$3:$B$7, SMALL(IF(COUNTIF($D$3:$D$8, $B$3:$B$7)=0, MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), ""), ROWS($A$1:A1))) To enter an array formula press and hold CTRL […]

### Compare two columns for same values

Compare two columns for same values [Excel Formula]

Question: How can I compare two columns to find values that exists in both cell ranges? The picture above shows […]

### Compare two columns and highlight matches

Compare two columns and highlight values in common

A conditional formatting formula highlights values in column B that also exist in column D. =COUNTIF($D$3:$D$7,B3) The same thing happens […]

### Match two columns and return a third

The following article is related and demonstrates how to find two values that match on the same row and return a third value on the same row:

INDEX MATCH with multiple criteria

The formula demonstrated in cell D13 is a regular formula, most people prefer a regular formula over an array formula […]

How to perform a two-dimensional lookup

Question: How would I go about looking up data in a cross-reference table. I have the header row (i.e. 24) […]

Use MEDIAN function to calculate overlapping ranges

I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it may be dates, […]

Nested IF statements in a formula are multiple combined IF functions so more conditions and outcomes become possible. They all are […]

The formula in cell B17 counts rows in cell range B3:D17 when at least one cell per row contains data. […]

How to replace part of formula in all cells

This article explains how to substitute part of a formula across all cells in a worksheet. It is easier than […]

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

How to perform a two-dimensional lookup

Question: How would I go about looking up data in a cross-reference table. I have the header row (i.e. 24) […]

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

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