Match two columns
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 return differences
The image above demonstrates an array formula in cell B11 that extracts values that only exist in List 1 (B3:B7) [โฆ]
Compare two columns and return differences
Compare two columns for same values
Extract shared values between two columns
Question: How can I compare two columns to find values that exists in both cell ranges? The picture above shows [โฆ]
Extract shared values between two columns
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 [โฆ]
Compare two columns and highlight values in common
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 [โฆ]
Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 [โฆ]
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 [โฆ]
Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with [โฆ]
Formula for matching a date within a date range
Table of contents Match a date when a date range is entered in a single cell Match a date when [โฆ]
Lookup multiple values across columns and return a single value
This article demonstrates how to get a value from a dataset based on multiple conditions across multiple columns. S.Babu asks: [โฆ]
Identify the position of a value in an array.
Extract table headers based on a condition
This article demonstrates an array formula that returns the table header based on a condition. For example, in cell C8 [โฆ]
Match a range value containing both text and numerical characters
Formula in cell C11: =LOOKUP(MID(C10, 4, 999)*1, MID(B3:B8, 4, 999)*1, D3:D8) This formula is an array formula. To enter an array [โฆ]
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
Paste image link to your comment.
English
Spanish
