How to vlookup to search for three same key words for bashing

Hello Good day everyone i'd like to seek help from you guys for a vlookup formula that will enable to search for 2 or 3 similar words in each cell then return the value as " matched " if not the " not matched " ....the purpose is to search for same company name in file [1] and file [2] and tagged it as " matched " if found matched and (((( OPTIONAL ) if possible will tagged " possible matched " if the suspected name somehow has similar but the not that sure))))..the problem is file [1] and file [2] has problem in naming convention: for example the other one is:

file [1] orange crawford inc. - file [2] orange crawford incorporated = in this scenario this should be matched..but my current vlookup formula the usual basic type searches only the EXACT words of each hence making the above example " not matched "

I provided an example scenario attached

Pls check the file attach, hope you satisfied



Azumi

hi azumi :D almost there..i am still learning on excel how do i use the cases there >.< especially on my case :P

in this formula:
=IFERROR(INDEX($A$2:$A$25,SMALL(IF(LEFT($A$28,5)=LEFT($A$2:$A$25,5),ROW($A$2:$A$25)-ROW($B$1)),ROW(A1:B1)),1),"")
where do i put the file[2] >.< considering i am bashing two different sheets..coz file 1 and 2 are different sheets :D

thanks for your time :P

Originally Posted by azumi

Pls check the file attach, hope you satisfied



Azumi

hi azumi :D almost there..i am still learning on excel how do i use the cases there >.< especially on my case :P

in this formula:
=IFERROR(INDEX($A$2:$A$25,SMALL(IF(LEFT($A$28,5)=LEFT($A$2:$A$25,5),ROW($A$2:$A$25)-ROW($B$1)),ROW(A1:B1)),1),"")
where do i put the file[2] >.< considering i am bashing two different sheets..coz file 1 and 2 are different sheets :D

thanks for your time :P

Matching 2 list

Azumi

currently testing both formula on my actual sheets....thanks for the time i really appreciate your efforts :D i'll give feedback and rep once done.. :D

Originally Posted by vlady

can you look also the other option on the attachement.
thanks.
Inside:
just to play safe i made 3 matches.
first matche -use the whole content of the cell.
second match - use the first two words of the cell with wild card
third match - use the first word of the cell with wild card
Attachment 276250

hi sir kababayan hehe thanks po :D ~ sorry i'm still learning on excel please let me know how to use your formula, what changes should i fill when using it in other sheets - thanks :D really appreciate your time po for this :D

@vlady wow thank you sir for your quick and decisive replies, it now worked and will reduce my bashing time for about 70% :D one last optional question sir hehehe if you have time..why did we remove index?

INDEX(File2!A:A,MATCH(A3,File2!A:A,0))

actually we do not need the "index" part there to shorten it up and return only "matched and not matched"..

@azumi
thanks again for all your replies..really appreciate and will use your formula for my excel studies..thanks for all those case example :D

You're wlcome, nice feedback....

Azumi

@azumi
@vlady

you're both welcome and i know you deserved more than that hehe :D

thanks for the nice tutorial and for being patient for a newbie excel user like me hehe i really wanted to learn excel and this is a warm welcome to me and hopefully i can teach others someday what you guys have inspired and thought me :D...seriously this bashing gave us a hardtime since we are only doing it manual on our division >.<...other software products cant even handle the searching too hehe..here's a quick example of other possibilities:

apple pumpkin corp.
apple pumpkin corporation
the apple pumpkin corp.
apple pumpkin orange corp. the
pumpkin apple orange corp. the
pumpkin orange apple corporation the
the orange apple pumpkin corporation
the apple orange pumpkin corp.

in my actual spread sheets i always encounter such combination ( originally business and company names )
the example i provided is a " matched "...to some extent the formula randomly gave me out of 60-70% accuracy, but it is really a BIG help and a big difference compared to what i originally use the =left/find (??,??)+1) and then i use vlookup to search them :D