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".

XXX	1
YYY	2
BBB	1
BBB	1
OOO	1
PPP	1

<tbody>
</tbody>

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?

XXX	YES
YYY	YES
YYY	YES
XXX	NO
BBB	YES
NNN	NO
EEE	NO
OOO	YES
BBB	YES
SSS	NO

<tbody>
</tbody>

 

[Andrew Poulsom]

Welcome to MrExcel.

Formula in E1 copied down:

Excel 2010

	A	B	C	D	E
1	XXX	1		XXX	YES
2	YYY	2		YYY	YES
3	BBB	1		YYY	YES
4	BBB	1		XXX	NO
5	OOO	1		BBB	YES
6	PPP	1		NNN	NO
7				EEE	NO
8				OOO	YES
9				BBB	YES
10				SSS	NO

<COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5"><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Sheet1​

Worksheet Formulas Cell Formula E1 =IF(SUMIF(A:A,D1,B:B)>=COUNTIF(D$1:D1,D1),"YES","NO") <THEAD> </THEAD><TBODY> </TBODY>

<TBODY>
</TBODY>

 

[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.