# IF(ISNA(VLOOKUP to match 1 column with another

#### stephnoven

##### New Member
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

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### arthurbr

##### Well-known Member
Try wrapping A2 with the trim() function

#### etaf

##### Well-known Member
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

##### New Member
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

##### Well-known Member

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)

Last edited:

#### etaf

##### Well-known Member
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
ABCDEFG
1Data SourcePromotion Takers DataCountTestVlook as writtencorrect Vlook
21212ASD1234ZXVTRUE101
31234ZXV1212ASDTRUE101
41168ASD0865JKKLFALSE010
51240THY1240THYTRUE101
61764NDH1584QTYUFALSE010
70865JKKLTRUE101
81234XRBYFALSE010
91097RTIBFALSE010
101584QTYUTRUE101
Sheet1
Cell Formulas
RangeFormula
D2:D10D2=COUNTIF(\$B\$2:\$B\$6,A2)>0
E2:E10E2=IF(COUNTIF(\$B\$2:\$B\$6,A2)>0,1,0)
F2:F10F2=IF(ISNA(VLOOKUP(A2,\$B\$2:\$B\$6,2,FALSE)),1,0)
G2:G10G2=IF(ISNA(VLOOKUP(A2,\$B\$2:\$B\$6,1,FALSE)),0,1)

#### stephnoven

##### New Member
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
ABCDE
1Data SourcePromotion Takers DataCountTest
21212ASD1234ZXVTRUE1
31234ZXV1212ASDTRUE1
41168ASD0865JKKLFALSE0
51240THY1240THYTRUE1
61764NDH1584QTYUFALSE0
70865JKKLTRUE1
81234XRBYFALSE0
91097RTIBFALSE0
101584QTYUTRUE1
Sheet1
Cell Formulas
RangeFormula
D2:D10D2=COUNTIF(\$B\$2:\$B\$6,A2)>0
E2:E10E2=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

##### Well-known Member
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
ABCDEFGH
1Data SourcePromotion Takers DataCountTestVlook as writtencorrect VlookVlookup
21212ASD1234ZXVTRUE1011212ASD
31234ZXV1212ASDTRUE1011234ZXV
41168ASD0865JKKLFALSE010#N/A
51240THY1240THYTRUE1011240THY
61764NDH1584QTYUFALSE010#N/A
70865JKKLTRUE1010865JKKL
81234XRBYFALSE010#N/A
91097RTIBFALSE010#N/A
101584QTYUTRUE1011584QTYU
Sheet1
Cell Formulas
RangeFormula
D2:D10D2=COUNTIF(\$B\$2:\$B\$6,A2)>0
E2:E10E2=IF(COUNTIF(\$B\$2:\$B\$6,A2)>0,1,0)
F2:F10F2=IF(ISNA(VLOOKUP(A2,\$B\$2:\$B\$6,2,FALSE)),1,0)
G2:G10G2=IF(ISNA(VLOOKUP(A2,\$B\$2:\$B\$6,1,FALSE)),0,1)
H2:H10H2=VLOOKUP(A2,\$B\$2:\$B\$6,1,FALSE)

Replies
3
Views
78
Replies
21
Views
300
Replies
4
Views
733
Replies
2
Views
154
Replies
1
Views
100

1,148,173
Messages
5,745,179
Members
423,931
Latest member
thangvan114

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back