+ Reply to Thread
Results 1 to 7 of 7

Trying to search a range

  1. #1
    Registered User
    Join Date
    03-14-2012
    Location
    Nebraska, USA
    MS-Off Ver
    Open Office
    Posts
    4

    Question 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.

    Thank you for your help,
    CrimRei
    Last edited by CrimRei; 03-14-2012 at 02:56 PM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,509

    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. #3
    Registered User
    Join Date
    03-14-2012
    Location
    Nebraska, USA
    MS-Off Ver
    Open Office
    Posts
    4

    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. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,509

    Re: Trying to search a range

    Okay, try this approach:

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

    Hope this helps.

    Pete

  5. #5
    Registered User
    Join Date
    03-14-2012
    Location
    Nebraska, USA
    MS-Off Ver
    Open Office
    Posts
    4

    Re: Trying to search a range

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

    CrimRei

  6. #6
    Registered User
    Join Date
    03-14-2012
    Location
    Nebraska, USA
    MS-Off Ver
    Open Office
    Posts
    4

    Re: Trying to search a range

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

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,509

    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:

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1