Trying to search a range

Can you be a bit more specific? This will return "found" or "absent" if the value in A2 is present anywhere in column C, but of course you could change those words to something else if you wish.

=IF(ISNA(MATCH(A2,C:C,0)),"absent","found")

Hope this helps.

Pete

More Specific. I wish to search cells from CI4:DG4 to see if any of these cells contains the number 1, if any of the cells (and it doesnt matter how many) contains the 1, I wish for it to return 3, is none of the cells contain a 1, I wish to have a return of 0.

Thank you for your help, I really appreciate it!!!!

CrimRei

Okay, try this approach:

=IF(COUNTIF(CI4:DG4,1)>0,3,0)

Hope this helps.

Pete

The selection I use has 6 ones in it, I get back Err:511

CrimRei

My apologies, that did work. How do I make this thread as solved?

You could also have used this:

=IF(ISNA(MATCH(1,CI4:DG4,0)),0,3)

(based on what I had given you originally). The following is taken from the FAQs:

To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save

In addition, you can pass on your thanks more directly by clicking the "star" icon in the bottom left corner of any post that has helped you.

Hope this helps.

Pete