If Value in Column B exists in Column A, return true, else, return false

Question

I have a question regarding comparing lists in Excel.

Basically, I want to see if the value in column B exists in the column A for all values in column B. If the does exist, I want to Return "Yes" beside that value in column C. I have tried:

=IFERROR(IF(MATCH(B2, A2:A5000),"Yes",),"No")

But this returns "Yes" for all values, even though I know for sure that this is not the case.

Is there something that I am doing wrong?

Any assistance is appreciated.

This portion `MATCH(B2, A2:A5000)` shall be `MATCH(B2, A2:A5000,0)` if you are looking for exact MATCH. — shrivallabha.redij, Jan 30 '18 at 16:10
Edit your question's title please. This will help others to find your questions easily. — Milad Rashidi, Jan 30 '18 at 16:22

score 0 · Answer 1 · answered Jan 30 '18 at 16:10

0

Consider:

=IFERROR(IF(MATCH(B2, A2:A5000,0),"Yes",),"No")

answered Jan 30 '18 at 16:10

Gary's Student

95,722
10
59
99

Thank you... I tried that but it still didn't work just right... realized what the correct code is: =IFERROR(IF(MATCH(B2, A$2:A$5000,0),"Yes",),"No") – Marko Dakic Jan 30 '18 at 16:30
Range array was not limited in Match()... so instead of A$2 i had A2... so when copying the function down the column it increased and therefore excluded some values – Marko Dakic Jan 30 '18 at 18:29
@MarkoDakic Very nice catch! – Gary's Student Jan 30 '18 at 18:30

score 0 · Answer 2 · answered Jan 30 '18 at 19:09

0

For a VBA solution that highlights duplicates and optionally spits out a list, see my answer at Good way to compare and highlight thousands of rows in VBA

answered Jan 30 '18 at 19:09

jeffreyweir

4,668
1
16
27

If Value in Column B exists in Column A, return true, else, return false

2 Answers2