Big Problem for me! Formula to find matched numbers

S

stakar

Hi!
I have a named cell -> combo_chk (cell J3)

This combo_chk changes because of FOR..NEXT from code but the style i
always like this : 4,5,12,40,45

I have 5 columns with values in the rows
A B C D E
1 12 40 41 45
5 6 10 11 40

I want to find if the 1 on column A is including in the combo_chk
then
if the 12 on column B is including in the combo_chk e.t.c.
If it is found return '1' otherwise return '0'.

Im using the above formula in a code which it passes to the cell
"=IF(ISERROR(FIND(C4,ComboChk)),0,1)"

But the problem is that it isnt findind the exact value. Eg. if th
combo_chk is '1,10,20,35,45'

----------------------- A B C D E
and the 1st row is 1 5 12 20 25

it will find the 1 twice because of the 1 and 10
the 5 three times because of the 5,35,45. etc

The formula that i want to use is like this

IF(OR(A1={4,5,12,40,45});1;0))
IF(OR(B1={4,5,12,40,45});1;0))
etc
But i cant pass the combo_chk in the formula. When i do that i get a
error

I hope you understand
Thanks
Stathi
 
J

Jason Morin

Assuming I understanding correctly, I would just
concatenate the 5 values together with a comma in between
each one, and then compare to combo_chk.

=IF(A1&","&B1&","&C1&","&D1&","&E1=combo_chk,1,0)

HTH
Jason
Atlanta, GA
 
S

stakar

No, i dont want that
What i want is:

I have 5 columns

A B C D E
10 11 12 15 40
5 9 10 12 30

and a cell (F3) named combo_chk
This cell (F3) has this string for example '10,2,5,8,40'

I want to find if any cell from columns ABCDE is in the string.
If its true return 1 otherwise return 0

So for the above columns the result will be
1 0 0 0 1
0 0 1 0 0

That's all
 
J

Jason Morin

Ah, now that's a little more clear. I understand. Try:

=ISNUMBER(SEARCH(","&A1&",",","&combo_chk&","))*1

and fill across 5 columns and down.

HTH
Jason
Atlanta, GA
 
Top