Match two columns to different columns

nelly9292

New Member
Joined
Jun 1, 2016
Messages
2
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 :
TitleAuthorIn stock?Lib-titleLib-author
AAAYesCCC
BBBYesDDD
CCCYesEEE
DDDYesFFF
EEEYesHHH
FFFYesIII
GGGYesGGG
HHHYesAAA
IIIYesBBB
KKKNo
LLLNo
MMMNo

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

63falcondude

Well-known Member
Joined
Jan 15, 2016
Messages
3,572
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
Joined
Jan 15, 2016
Messages
3,572
The countif formula would look as follows:

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

63falcondude

Well-known Member
Joined
Jan 15, 2016
Messages
3,572
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.
 

Forum statistics

Threads
1,084,962
Messages
5,380,841
Members
401,699
Latest member
ijazkhan01

Some videos you may like

This Week's Hot Topics

Top