Excel Formula?

Question

I am looking for a formula or way to do the following in Excel:

I have a value, and I want to see if it is contained in an array. I just want a 1 or 0 if it is in the array or not. I could use any of the LOOKUP functions, but they return error messages when the value is not found.

Example:

Array:
101
102
103
104
105

For the value 101, I would like a "1", because it is contained in the array. For the value 108, I would like a 0 (or anythng else I can flag) because it is not contained in the array...

Any ideas?

Answer 1

Assuming your "Value" is in cell A1 and your "Array" is in "B1:B5" then the following formula in cell C1 would give you your desired flag. Hold cursor over formula to display entire formula.
=IF(ISERROR(VLOOKUP(A1,B1:B5,1,FALSE))=FALSE,1,0)

Answer 2

SQL would probably be best in this situation. You could use Microsoft Acess to store you list of numbers (arrays) you would write an SQL query to search the array. It would be something like this.....

Select * from Table1
Where Array=Number

Where Array is the name of the table heading and the Number is the number your searching. You can write a stored procedure to return a 0 or 1 and applicable.

Answer 3

What exactly are you searching for in this array??

Surely you need to just adapt:

If (B6 = '101' THEN 1 else 0)

Answer 4

I don't know but I suggest, for programming questions, use the programming newsgroups, you will find answers easier. Go to this page:

http://groups.google.com/groups/dir?lnk=hpsfg&hl=en&q=excel

Try the groups called:
microsoft.public.excel
microsoft.public.excel.programming

Answer 5

the funcion you are wanting to use is VLOOKUP, within an IF clause ; check it out in EXCEL help. very powerful

Answer 6

Try This
IF(ISERROR(VLOOKUP
(lookupvalue,Array,1,FALSE)),
0,1)

This might be fits your suits