Excel Formula Question

[Grahamfff 10]

Hi

 

I have two columns of numbers like this:

 

A     B       C

2     3

5     8

8     10 

10   16

15    18

20     21

 

In column C I'd like a formula which tells me whether or not the number in column A also appears in column B (not necessarily on the same row)

 

So:

 

A     B       C

2     3      0

5     8     0

8     10    1

10   16    1

15    18   0

20     21   0

 

I would have expected this in column C =countif(A2,B$2:B$6) would do the job, but it doesn't. What am I doing wrong?

 

Thanks!

 

Edited July 3, 2020 by Grahamfff

[iansheff 88]

Try this link, not tried it myself though.

https://www.techjunkie.com/two-cells-contain-same-value/

Edited July 3, 2020 by iansheff

[altus 540]

You've got the range and criteria in the wrong order. It should be =countif(B$2:B$6,A2)

Note: That will give 0 for none and a number >0 depending on how many of the values it finds so you might want to wrap it in something that gives a yes/no answer.

[Grahamfff 10]

Altus - that worked - thank you.