# Match two columns to different columns

#### nelly9292

##### New Member
I am trying to find a formula that will look at two columns in one row and find that pair in different columns..

Basically I am looking for a formula that will generate the middle column. If "column A and Column B" pair matches "column D and column E", column C will return YES

Example :
 Title Author In stock? Lib-title Lib-author A AA Yes C CC B BB Yes D DD C CC Yes E EE D DD Yes F FF E EE Yes H HH F FF Yes I II G GG Yes G GG H HH Yes A AA I II Yes B BB K KK No L LL No M MM No

<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>

#### barjoman

##### Board Regular
The formula in C2 should be:

Code:
``=IF(A2&B2=D2&E2,"Yes","No")``

#### 63falcondude

##### Well-known Member
There's definitely a prettier way than this but you can create 2 helper columns (I'll make them columns C and G) with the formuls =A2&" "&B2 and =E2&" "&F2. Now use a countif formula to count the number of times a value in column C shows up in column G.

Last edited:

#### 63falcondude

##### Well-known Member
The countif formula would look as follows:

=IF(COUNTIF(\$G\$2:\$G\$10,C2)>0,"Yes","No")

#### 63falcondude

##### Well-known Member
Better formula in C2 =IF(ISNUMBER(MATCH(A2&B2,\$D\$2:\$D\$10&\$E\$2:\$E\$10,0)),"Yes","No") Ctrl Shift Enter to make it into an array formula.