Does a number exist in an array (Excel 2000)

Topic

New Reply

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

Reply | Quote

Replies

=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?

Reply | Quote

You could also consider the MATCH() function. If your list of numbers is in A1:A10, the the formula

	  =MATCH(C1,A1:A10,0)

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

	  =SUM(IF(ISNA(MATCH(C1:E1,A1:A10,0)),0,1)) 

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

Reply | Quote