| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup or index(match)?
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup or index(match)?
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 On Dec 19, 6:38*pm, NewAccessDude wrote: 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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup or index(match)?
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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "NewAccessDude" wrote: 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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup or index(match)?
Maybe this:
=IF(COUNTIF(A$2:A$100,"*"&B2&"*"),"Found","Not Found") -- Biff Microsoft Excel MVP "NewAccessDude" wrote in message ... 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! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup or index(match)?
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. "Pete_UK" wrote: 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 On Dec 19, 6:38 pm, NewAccessDude wrote: 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! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup or index(match)?
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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "NewAccessDude" wrote: 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. "Pete_UK" wrote: 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 On Dec 19, 6:38 pm, NewAccessDude wrote: 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! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup or index(match)?
Thanks for the all help, it looks like a few of them will work so i have
choices. "T. Valko" wrote: Maybe this: =IF(COUNTIF(A$2:A$100,"*"&B2&"*"),"Found","Not Found") -- Biff Microsoft Excel MVP "NewAccessDude" wrote in message ... 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! |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup or index(match)?
You're welcome!
-- Biff Microsoft Excel MVP "NewAccessDude" wrote in message ... Thanks for the all help, it looks like a few of them will work so i have choices. "T. Valko" wrote: Maybe this: =IF(COUNTIF(A$2:A$100,"*"&B2&"*"),"Found","Not Found") -- Biff Microsoft Excel MVP "NewAccessDude" wrote in message ... 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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| vlookup or index match??? | Excel Discussion (Misc queries) | |||
| Index/Match or Vlookup | Excel Worksheet Functions | |||
| index Match, or Vlookup Match.. | Excel Worksheet Functions | |||
| Need Help with Index and Match or Vlookup | Excel Worksheet Functions | |||
| VLookup or Index Match ? | Excel Worksheet Functions | |||
Linear Mode
