Formula for return a "1" if certain numbers are found

[cl604]

Hi guys

I am using Excel 2011.

I am trying to make a formula that will return a 1 if certain numbers like 26, 52, 35 etc up to 20 different ones are found in Column Ac1 down to Ac500. If no numbers are found then it just says 0.

I tried this formula but it does not do multiples IF(ISNA(VLOOKUP(26,$AC$5:$AC$15,0,FALSE)),"1","0")
I am thinking now that this formula is not the way to go.

thoughts?

 

[Rick Rothstein]

From the OPs response to my question about duplicates, I think that 199, 10, 26, 199 would meet the condition.
The formula would also give an improper FALSE to my sequence in #16.

Good point... in my "haste", I had ignored the fact that MATCH will only find the first occurrence of the value.

It seems to me that the OP's question could be restated "Given a sequence of numbers, is 26, 199, 10 a sub-sequence?" (Add in the OP's preference for a sequence that goes upward)
That is the question that Rick's UDF answers.

I posted the UDF mostly as an aside, but in thinking about the OP's question more carefully, the UDF may be the only practical answer.

 

[mikerickson]

Your UDF is a better solution than my three Name approach.

 

[cl604]

this is formula looks correct =ISNUMBER(COUNTA(Below26Below199Below10). The sequence must apperar as 26 comes out first then 199 then 10. Bit like bingo i guess.I just want to restrict it to ac:ac300 how do i do that?

 

[mikerickson]

Change the references in the names from A1 to AC1 and A100 to AC300

 

[cl604]

Thanks for your help guys.
I feel like and idiot but the formula's are producing an error.This formula looks correct but does produce an error =ISNUMBER(COUNTA(Below26Below199Below10).
Can we restrictq it to the cells ac4:ac4300.

 

[cl604]

Ok thanks for the replies. The formula =ISNUMBER(COUNTA(Below26Below199Below10) has an error at countA and i am lost to how to fix it.The sequence is correct I just need to define the range from Ac1 to ac 300. Could you be so kind to redo the formula to show the range.

great wok thanks

 

[mikerickson]

The change has to be made in the definiton of the names, not the final formula.

BTW, there is a right parenthesis missing from the formula in post #26.

 

[cl604]

mikerickson thanks for your help. Appreciate it. I am a little lost but. The formula's you stated before i tried to put them together and got this
=ISNUMBER(COUNTA(INDEX(INDEX(INDEX(Ac1:Ac100, MATCH(10,Ac1:Ac100,0), 1):Ac100 , MATCH(199,INDEX(Ac1:Ac100, MATCH(10,Ac1:Ac100,0), 1):Ac100 , 0), 1):Ac100, MATCH(26,INDEX(INDEX(Ac1:Ac100, MATCH(10,Ac1:Ac100,0), 1):Ac100, MATCH(199,INDEX(Ac1:Ac100, MATCH(10,Ac1:Ac100,0), 1):Ac100 , 0), 1):A100, 0),1), 1):Ac100))
I can not define a name on the spreadsheet or another sheet due to the sheets constantly being imputed with data an thus the formula would be lost. I can only put one formula in a certain box so i put what you had together. However I don't think this is what you had in mind.We are on the right track though. So close.
Do you have any ideas to work around this.Cheers

 

[mikerickson]

Let me re-structure the Names to accomidate inserted rows. Inserted columns will require the use of a volatile function.

Name: BelowFirst10
RefersT0: =INDEX($AC:$A$C, MATCH(10,$AC:$AC,0), 1):INDEX($A$C, 100,1)

Name: Below199Below10
RefersTo: = INDEX(Below10, MATCH(199, Below10, 0), 1):INDEX($A$C, 100,1)

Name: Below26Below199Below10
RefersTo: =INDEX(Below199Below10, MATCH(26, Below199Below10, 0),1), 1):INDEX($A$C, 100,1)

 

[cl604]

Thanks mikerickson. So achieve 26 is first then 199 and finally 10 I would use the formula "Below26Below199Below10"
RefersTo: =INDEX(Below199Below10, MATCH(26, Below199Below10, 0),1), 1):INDEX($A$C, 100,1) Where it says Below199Below10 I would cut and paste the formula "Below199Below10" which is
RefersTo: = INDEX(Below10, MATCH(199, Below10, 0), 1):INDEX($A$C, 100,1) and then once this is in where it says Below 10 I would cut and paste "BelowFirst10"
RefersT0: =INDEX($AC:$A$C, MATCH(10,$AC:$AC,0), 1):INDEX($A$C, 100,1)

Is this correct or am I on the wrong track. sorry to be a pain.