IF(ISNA(VLOOKUP to match 1 column with another

stephnoven

New Member
Joined
May 2, 2021
Messages
3
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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 SourcePromotion Takers Data
1212ASD1234ZXV
1234ZXV1212ASD
1168ASD0865JKKL
1240THY1240THY
1764NDH1584QTYU
0865JKKL
1234XRBY
1097RTIB
1584QTYU

Thank you in advance!
 

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

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,109
Office Version
  1. 365
Platform
  1. MacOS
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
Joined
May 2, 2021
Messages
3
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Oct 24, 2012
Messages
5,109
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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
Joined
Oct 24, 2012
Messages
5,109
Office Version
  1. 365
Platform
  1. MacOS
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
Joined
May 2, 2021
Messages
3
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Oct 24, 2012
Messages
5,109
Office Version
  1. 365
Platform
  1. MacOS
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)
 
Solution

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top