# Trying to search a range

1. ## Trying to search a range

This is a noob question, but I have looked in tutorials and over the net and can not find the answer.
I am trying to search a range of cells to see if any of them contain a certain number, if so to return with a specified value, and if not, return with a different specified value.

CrimRei

2. ## Re: 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

3. ## Re: Trying to search a range

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

4. ## Re: Trying to search a range

Okay, try this approach:

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

Hope this helps.

Pete

5. ## Re: Trying to search a range

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

CrimRei

6. ## Re: Trying to search a range

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

7. ## Re: Trying to search a range

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:

- Go to the first post
- Click edit
- 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

There are currently 1 users browsing this thread. (0 members and 1 guests)