Excel - Data Matching Between two sheets

[PikK45]

All,

I have been using VLOOKUP for a long time now. But, there are many limitations to that. This one below is an example where I couldn't complete with VLOOKUP.

Lets say we have 6x2 rows in "sheet1". [TABLE="width: 100"]
<tbody>[TR]
[TD]XXX[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]YYY[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]BBB[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]BBB[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]OOO[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]PPP[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

Now, I have 10x2 rows in "sheet2". I have to search for col1[sheet2] in col1[sheet1]. If it is present, then I should put "YES" in col2[sheet2]. Below are the scenarios, where I get trouble.

• XXX value on sheet1 is just1. So in Sheet2, I should get "YES" for only 1 row. But on VLOOKUP, I get "YES" for both.
• BBB is present twice in sheet1. No matter how many times BBB occur in sheet2, I get YES for all

Is there a method to over come this limitation of VLOOKUP? Any other script or formula to get this thing done?

[TABLE="width: 100"]
<tbody>[TR]
[TD]XXX[/TD]
[TD]YES[/TD]
[/TR]
[TR]
[TD]YYY[/TD]
[TD]YES[/TD]
[/TR]
[TR]
[TD]YYY[/TD]
[TD]YES[/TD]
[/TR]
[TR]
[TD]XXX[/TD]
[TD]NO[/TD]
[/TR]
[TR]
[TD]BBB[/TD]
[TD]YES[/TD]
[/TR]
[TR]
[TD]NNN[/TD]
[TD]NO[/TD]
[/TR]
[TR]
[TD]EEE[/TD]
[TD]NO[/TD]
[/TR]
[TR]
[TD]OOO[/TD]
[TD]YES[/TD]
[/TR]
[TR]
[TD]BBB[/TD]
[TD]YES[/TD]
[/TR]
[TR]
[TD]SSS[/TD]
[TD]NO[/TD]
[/TR]
</tbody>[/TABLE]

 

[Andrew Poulsom]

Welcome to MrExcel.

Formula in E1 copied down:

Excel 2010

	A	B	C	D	E
XXX	XXX	YES
YYY	YYY	YES
BBB	YYY	YES
BBB	XXX	NO
OOO	BBB	YES
PPP	NNN	NO
EEE	NO
OOO	YES
BBB	YES
SSS	NO

<COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5"><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]1[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</TBODY>

Sheet1​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<THEAD>[TR="bgcolor: #dae7f5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</THEAD><TBODY>[TR]
[TH="width: 10, bgcolor: #dae7f5"]E1[/TH]
[TD="align: left"]=IF(SUMIF(A:A,D1,B:B)>=COUNTIF(D$1:D1,D1),"YES","NO")[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[rememberthis]

Two ways:
1) Write an "IF" statement, the logic being:
if Yes then "1"
else "2"

2) Make an additional column next to the values you want and ask your VLOOKUP to bring that column instead -- this column could say whatever you need it to.

Hope that helps.

 

[pjmorris]

Try this: =IF(COUNTIF($A$1:$A1,A1)<=SUMIF(Sheet1!$A$1:$A$6,A1,Sheet1!$B$1:$B$6),"Yes","No")

 

[PikK45]

Thank you very much for the express reply... ray:

 

[pjmorris]

A pleasure to help - and welcome to the forum.