Does A Value in Column A Exists in column B Yes/No should be in Column C

maslam15

New Member
Joined
Aug 21, 2019
Messages
15
Hello Everyone,
I am trying to find out the formula if the values I have in Column A Exists in Column B Should Say Yes/No in Column C. Please see an excel table below.


ID

ID2Does ID in column A exist in Column B

112

112Yes
111115No
113114Yes
114113Yes
Thank you,
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,737
Office Version
  1. 365
Platform
  1. Windows
How about
=if(isnumber(match(a2,b$2:b$10,0)),"Yes","No")
 

denzo36

Board Regular
Joined
Sep 22, 2019
Messages
149
Office Version
  1. 365
Platform
  1. Windows
Hi try

={IF(OR(A2=$B$2:$B$5),"Yes","No")} - enter formula with Ctrl+Shift+Enter
 

ahennema

New Member
Joined
Sep 14, 2005
Messages
34
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I just ran the above formula: IF(OR(A2=$B$2:$B$5),"Yes","No") and did not need the CTRL+SHIFT+ENTER, in fact it did not work with CTRL+SHIFT+ENTER
 

denzo36

Board Regular
Joined
Sep 22, 2019
Messages
149
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You can enter the formula without ctrl+shift+enter but it will not work, you need to ctrl+shift+enter as it's an array formula. You may need to adjust your range for column B if it's larger than the range shown in your post.

The formula should be in column C2 and filled down. I did it here and it works fine
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,737
Office Version
  1. 365
Platform
  1. Windows
If you have dynamic arrays, then you don't need to use CSE
+Fluff.xlsm
ABCD
1DID2Does ID in column A exist in Column B
2112112YesYes
3111115NoNo
4113114YesYes
5114113YesYes
Report
Cell Formulas
RangeFormula
C2:C5C2=IF(ISNUMBER(MATCH(A2,B$2:B$10,0)),"Yes","No")
D2:D5D2= IF(OR(A2=$B$2:$B$5),"Yes","No")
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,737
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks fro the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,128,052
Messages
5,628,315
Members
416,311
Latest member
S991102

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