Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Nov 2002
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Does a number exist in an array (Excel 2000)

    Hi

    Is there a function that will tell me if a number exists in a list. for example:
    I have a list of numbers -
    1
    4
    6
    8
    14
    47
    3
    8
    and I want to find out if any of these numbers are in the list
    5
    7
    8

    is there a simple command that will do this? Most that I've looked at fail if the number isn't there and simply return n/a - and I can't do anything with that.

    Bob

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Does a number exist in an array (Excel 2000)

    =COUNTIF(range,number) will count the number of cells in which the digit "number" is found, and will return zero if the digit is not found, so you can treat the result as boolean, where zero = FALSE, and any non-zero number is treated as TRUE. Will that work?
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Does a number exist in an array (Excel 2000)

    You could also consider the MATCH() function. If your list of numbers is in A1:A10, the the formula <pre> =MATCH(C1,A1:A10,0)</pre>

    will return the position of a value in C1 in the range A1:A10 or #N/A if it cannot be found.
    Or if you want to report the number of matches of one range in another you could use an array formula like <pre> =SUM(IF(ISNA(MATCH(C1:E1,A1:A10,0)),0,1)) </pre>

    which will return the number of matches for the numbers in range (C1:E1) in th e range (A1:A10). When you have that formula typed in you must press Ctrl Shift and Enter together so that it is enetered as an array formula. The whole formual should then appear in { } brackets.

    Andrew C

Posting Permissions

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