Using list to check value of a cell

I have a problem when i'm trying to check the contents of a cell.

The list contain part name the refers to different models of the same product.

i.e.
List would say ABC and that would refer to Products ABC123 & ABC456.


=IF(ISNUMBER(FIND(mylist,d1)),"Y","N")
However, it only seems to pick up the 1st cell in the list.

I can use

=OR(EXACT(mylist,d1))

But then the list has to contain the the exact product (abc123, abc456, etc...)


Any idea how i can get this to work?

I don't know of a single formula that will return multiple results, if that is what you are trying to do. You can have a column of formulas beside the column of data that look for any product numbers containing what you're searching for, then compilation formulas using match and indirect to create a unified list. I would need to see your workbook to understand this any further.

It's likely that I'm not really understanding what you're trying to accomplish. A potential solution for you is the following formula:
=Match("*"&D1&"*",mylist,0)

This will return the first row where the value in Cell D1 is found within any value contained in "mylist" (I'm assuming that is a named range?)

Thanks for the reply however both:

=IF(SUMPRODUCT(--ISNUMBER(FIND(MyList,D1))),"Y","N")
=IF(ISNA(LOOKUP(2^15,FIND(MyList,D1))),"n","y")

Now add a Y for all products weaver it contains a name from the list or not?

bmxfreedom,

I have a colum of product name and another colum that check to see if the product is contained in a list or not, adding a Y if it is and an N if it's not.

The problem being that list contains part of the name as the list would be too big if i added all the possiblities.

=Match("*"&D1&"*",mylist,0)
Returns a vaule of #N/A

I've may a file with an example of what i'm trying to do, as the original file has customer sensertive data in and is way to big.

Pls check the files


Azumi

Oh, I see it now. Are the left 3 letters consistently 3? To make this work, you'll need a definable split between the list characters and the model numbers that follow them no matter how long they are. It's easiest with consistency, so if they are always 3 characters, the formula = if(isnumber(match(left(d1,3),mylist,0)),"Y",N") will function just fine for you (given case consistency as well...)

Also, another possible part of your problem here is that your named range "mylist" is referring to a single cell at the top of the list.

And actually, with that fixed, the formula =IF(SUMPRODUCT(--ISNUMBER(FIND(mylist,D2))),"Y","N") works just fine as far as I can tell without any of the weirdness of splitting up the model number with left(). Change your named range and try it out.

Thanks Azumi,

But doesn't work when i transfer it across as some of my product has more than 3 characters.

for example:

Product = CPP RNC 3820 search list would be looking for RNC
or
Product = SUN OSS-RC 13A search list would be looking for OSS-RC

Originally Posted by benishiryo

=IF(SUMPRODUCT(--ISNUMBER(FIND(mylist,D1))),"Y","N")

This is a fantastic formula benishiryo! Thanks. I'm sure to put it to use some day!

ewiandr, don't worry about the left 3 characters. This formula works great. Just make sure your named range "mylist" is actually the whole list this time.

Originally Posted by bmxfreedom

And actually, with that fixed, the formula =IF(SUMPRODUCT(--ISNUMBER(FIND(mylist,D2))),"Y","N") works just fine as far as I can tell without any of the weirdness of splitting up the model number with left(). Change your named range and try it out.

The list was a typeo, the correct list is correct, the problem seems to be because i left some blank cells in my list to try an make it more dynamic if i need to add any more.

Once i remove the empty cells your formula works fine, thanks...

Interesting. I haven't tested this on a list with blanks, so I don't know why that would be the case. Good to know.