Match two columns to different columns

[nelly9292]

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]

The formula in C2 should be:

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

 

[63falcondude]

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.

 

[63falcondude]

The countif formula would look as follows:

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

 

[63falcondude]

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.