Match a number in an array

Posted by Scott on August 12, 2001 3:11 PM

I want to be able to match a number in a cell with a number from an array. If there is a match, I want to return a " " value....How can I do this? Example....

A......B.....C.....D.....E
1 7 9 11 13
2 8 17 20 25
3 4 16 30 34
4 11 22 25 38
5 2 14 19 21

VALUE TO MATCH: 30
If 30 is listed in the range (A1:e5), then I want to return a Blank cell...If it doesn't match a number in the list, Then I want to return the Value 30....

Can anyone help?
Thanks, Scott

Posted by Aladin Akyurek on August 12, 2001 3:18 PM

Scott,

=IF(COUNTIF(A1:E5,30)=1,"",30)

I'd put that 30 in some unused cell (say, in F1) and change the above formula to:

=IF(COUNTIF(A1:E5,F1)=1,"",F1)

Note. I used "" (meaning: empty/blank), not " " (meaning: a single space) as you do. If your " " is intentional, disregard this note.

Aladin