vlookup or index(match)?

[NewAccessDude]

Hello,

I have been playing with both the vlookup and the index(match) functions and
I need some help.

I have 1 column of data and I need to see if 13 words are contained anywhere
in that column. For example: I need to see if anything from column B is in
Column A. Any and all help would be appreciated

Column A (just a sample) Column B
6-Hexanelactam (epsilon-caprol Aircraft
6-Hexanelactam (epsilon-caprol atv
6-Hexanelactam (epsilon-caprol Auto
6-Hexanelactam (epsilon-caprol Bars
6-Hexanelactam (epsilon-caprol boat
6-Hexanelactam (epsilon-caprol Corn
6-Hexanelactam (epsilon-caprol Forklift
6-Hexanelactam (epsilon-caprol grain
6-Hexanelactam (epsilon-caprol Household
6-Hexanelactam (epsilon-caprol motorcycle
6-Hexanelactam (epsilon-caprol Personal
6-Hexanelactam (epsilon-caprol Scrap
6-Hexanelactam (epsilon-caprol soybean
Acyclic monoamines, their deri
Agricultural/horticultural/for
Aluminum foil not backed not o
Aluminum foil not backed not o
Aluminum foil not backed not o
Aluminum plates, sheets & stri
Antennas and antenna reflector
Antennas and antenna reflector
Antennas and antenna reflector
Antennas and antenna reflector
Apple juice, unfermented
Apple juice, unfermented
Apple juice, unfermented
Apple juice, unfermented
Apple juice, unfermented
Articles & equipment for sport
Articles for pocket or handbag
Articles of apparel & clothing
Articles of apparel & clothing
Articles of apparel & clothing
Articles of iron or steel
Articles of iron or steel
Articles of iron or steel
Articles of iron or steel
Articles of iron or steel
Articles of iron or steel

thanks!

 

[Pete_UK]

You can put this in C1:

=IF(ISNA(MATCH(B1,A:A,0)),"No","Yes")

and then copy down to C13 - It will check for any matches with the
word in column B and return Yes or No as appropriate.

Hope this helps.

Pete

 

[Luke M]

If the words you're looking for is exactly what would be found in column A,
then
=IF(ISNUMBER(MATCH(B2,A2:A100,0)),"Found","Not Found")
entered into C column and copied down would let you know if a word from
column B was found in your list.

 

[T. Valko]

Maybe this:

=IF(COUNTIF(A$2:A$100,"*"&B2&"*"),"Found","Not Found")

 

[NewAccessDude]

Thanks for the quick reply Pete

However it doesn't appear to be working, I am getting NO for all 13 items
but I should have Yes for 1. In column A my test is Household articles &
parts as well as Parts & Household articles. and Household is in column B.

I was hoping there is a formula or something where I can match the word
Household regardless of where it appears. I have played with the wildcards
but nothing I could come up worked.

 

[Luke M]

Ah, you're looking within a word.

=IF(SUMPRODUCT(ISNUMBER(FIND(B2,$A$2:$A$100))*1)>0,"Found","Not Found")

Again, place in C2, copy down.

 

[NewAccessDude]

Thanks for the all help, it looks like a few of them will work so i have
choices.

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Thanks for the all help, it looks like a few of them will work so i have
choices.