Jump to content
Fancy running a forum? Sheffield Forum is for sale! Learn more

Excel Formula Question

Recommended Posts

Posted (edited)

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 by Grahamfff

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Altus - that worked - thank you.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.