IF(ISNA(VLOOKUP to match 1 column with another

[stephnoven]

So I moved the data that I want to use into 1 sheet and I want to compare data on Column 1 with Column B.
I want to find out if the data on Column 1 also exist in Column 2.
I am using "1" for exist, and "0" for not existing.

The problem is, first I tried using my office notebook, but all the results show "1" (Excel 2013)
Then, I tried the formula with my personal notebook, but using sample data, since I can't transfer the data to outside, and success (Excel 365)
I'm not sure whether the Excel version affects the result.

Formula that I used:
=IF(ISNA(VLOOKUP(A2,$B$2:$B$6,2,FALSE)),1,0)

Sample:

Data Source	Promotion Takers Data
1212ASD	1234ZXV
1234ZXV	1212ASD
1168ASD	0865JKKL
1240THY	1240THY
1764NDH	1584QTYU
0865JKKL
1234XRBY
1097RTIB
1584QTYU

Thank you in advance!

 

[arthurbr]

Try wrapping A2 with the trim() function

 

[etaf]

so 100% clear , from your example
=IF(ISNA(VLOOKUP(A2,$B$2:$B$6,2,FALSE)),1,0)

I would use a countif() > 0

you want to see if column A - heading "DATA SOURCE"
are in the Column B - heading "Promotion Takers Data"

And looking at the list in B all exist in A
so a 1 for all those value in B would be correct

 

[stephnoven]

so 100% clear , from your example
=IF(ISNA(VLOOKUP(A2,$B$2:$B$6,2,FALSE)),1,0)

I would use a countif() > 0

you want to see if column A - heading "DATA SOURCE"
are in the Column B - heading "Promotion Takers Data"

And looking at the list in B all exist in A
so a 1 for all those value in B would be correct

I want to edit my post to give more explanation, but I'm quite lost.

The results that I'm trying to find is that, whether the data in "Data Source", also exist in "Promotion Takers Data"
In the real data, all the results show "1", even though not all the data in "Data Source" exist in another.

I'm using the exact formula for both devices, not sure why show different results.

 

[etaf]

Edit - only available for about 10mins

then a countif() should work -
if you are looking at the values In A and comparing with B column , then vlookup is not the best as the range is B and you are using 2 - which is C
But you want want 1 to see if in the column

=IF(ISNA(VLOOKUP(A2,$B$2:$B$6,1,FALSE)),1,0)
Also that will test for an error, so NOT in the list will return a 1 ?


try
=IF(COUNTIF($B$2:$B$6,A2)>0,1,0)

 

[etaf]

then a countif() should work -
if you are looking at the values In A and comparing with B column , then vlookup is not the best as the range is B and you are using 2 - which is C
But you want want 1 to see if in the column

=IF(ISNA(VLOOKUP(A2,$B$2:$B$6,1,FALSE)),1,0)
Also that will test for an error, so NOT in the list will return a 1

try
=IF(COUNTIF($B$2:$B$6,A2)>0,1,0)

Book1
	A	B	C	D	E	F	G
1	Data Source	Promotion Takers Data		Count	Test	Vlook as written	correct Vlook
2	1212ASD	1234ZXV		TRUE	1	0	1
3	1234ZXV	1212ASD		TRUE	1	0	1
4	1168ASD	0865JKKL		FALSE	0	1	0
5	1240THY	1240THY		TRUE	1	0	1
6	1764NDH	1584QTYU		FALSE	0	1	0
7	0865JKKL			TRUE	1	0	1
8	1234XRBY			FALSE	0	1	0
9	1097RTIB			FALSE	0	1	0
10	1584QTYU			TRUE	1	0	1
Sheet1

Cell Formulas
Range	Formula
D2:D10	D2	=COUNTIF($B$2:$B$6,A2)>0
E2:E10	E2	=IF(COUNTIF($B$2:$B$6,A2)>0,1,0)
F2:F10	F2	=IF(ISNA(VLOOKUP(A2,$B$2:$B$6,2,FALSE)),1,0)
G2:G10	G2	=IF(ISNA(VLOOKUP(A2,$B$2:$B$6,1,FALSE)),0,1)

 

[stephnoven]

then a countif() should work -
if you are looking at the values In A and comparing with B column , then vlookup is not the best as the range is B and you are using 2 - which is C
But you want want 1 to see if in the column

=IF(ISNA(VLOOKUP(A2,$B$2:$B$6,1,FALSE)),1,0)
Also that will test for an error, so NOT in the list will return a 1 ?


try
=IF(COUNTIF($B$2:$B$6,A2)>0,1,0)

Book1
	A	B	C	D	E
1	Data Source	Promotion Takers Data		Count	Test
2	1212ASD	1234ZXV		TRUE	1
3	1234ZXV	1212ASD		TRUE	1
4	1168ASD	0865JKKL		FALSE	0
5	1240THY	1240THY		TRUE	1
6	1764NDH	1584QTYU		FALSE	0
7	0865JKKL			TRUE	1
8	1234XRBY			FALSE	0
9	1097RTIB			FALSE	0
10	1584QTYU			TRUE	1
Sheet1

Cell Formulas
Range	Formula
D2:D10	D2	=COUNTIF($B$2:$B$6,A2)>0
E2:E10	E2	=IF(COUNTIF($B$2:$B$6,A2)>0,1,0)

Thank you, I tried using the Countif formula and it works.

Then, for your question about the error, yes, when I tried with the original data at my office notebook using the same formula and same layout, the differences are the original data have longer number and more rows. All results show 1, even though not all are in the list. Same with the sample data, the data in "Promotion Takers" column, in the original data is less than "Data Source"

 

[etaf]

the original formula
Is looking in column B for a match , then returning the value in column C
It only needs to return the value in B
so a 1 is used instead of a 2
=IF(ISNA(VLOOKUP(A2,$B$2:$B$6,1,FALSE)),1,0)
Then if vlookup cannot find the value of A2 within the B column , then it returns an ERROR - NA
Your IF checks to see if there is a NA - ISNA() - if there is an error , then returns a TRUE
so you get a 1 from the IF where Vlookup does not match , returns an NA and then ISNA returns a true so 1

Thank you, I tried using the Countif formula and it works.

So countif() works , then you are good to go
Or correct the vlookup formula
Also make sure no spaces - in the data , as suggested using a trim

Heres the vlookup results , using 1 instead of 2

Book1
	A	B	C	D	E	F	G	H
1	Data Source	Promotion Takers Data		Count	Test	Vlook as written	correct Vlook	Vlookup
2	1212ASD	1234ZXV		TRUE	1	0	1	1212ASD
3	1234ZXV	1212ASD		TRUE	1	0	1	1234ZXV
4	1168ASD	0865JKKL		FALSE	0	1	0	#N/A
5	1240THY	1240THY		TRUE	1	0	1	1240THY
6	1764NDH	1584QTYU		FALSE	0	1	0	#N/A
7	0865JKKL			TRUE	1	0	1	0865JKKL
8	1234XRBY			FALSE	0	1	0	#N/A
9	1097RTIB			FALSE	0	1	0	#N/A
10	1584QTYU			TRUE	1	0	1	1584QTYU
Sheet1

Cell Formulas
Range	Formula
D2:D10	D2	=COUNTIF($B$2:$B$6,A2)>0
E2:E10	E2	=IF(COUNTIF($B$2:$B$6,A2)>0,1,0)
F2:F10	F2	=IF(ISNA(VLOOKUP(A2,$B$2:$B$6,2,FALSE)),1,0)
G2:G10	G2	=IF(ISNA(VLOOKUP(A2,$B$2:$B$6,1,FALSE)),0,1)
H2:H10	H2	=VLOOKUP(A2,$B$2:$B$6,1,FALSE)