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

Match two columns.xlsx

Compare two columns for differences

This article extracts values that only exists in one column:

Compare two columns and return differences

Compare two columns for same values

Extract shared values between two columns

Compare two columns and highlight matches

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