SOLVED
Home

VLOOKUP that searches for suffixes to a word...

%3CLINGO-SUB%20id%3D%22lingo-sub-1115884%22%20slang%3D%22en-US%22%3EVLOOKUP%20that%20searches%20for%20suffixes%20to%20a%20word...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1115884%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20really%20hope%20someone%20can%20help%20me%20with%20this.%20I've%20been%20searching%20for%20hours%20in%20multiple%20forums%2C%20but%20I%20can't%20find%20a%20solution%20to%20my%20problem.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20A%20is%20a%20list%20of%20english%20words.%20Column%20B%20is%20a%20list%20of%20english%20suffixes.%20How%20would%20I%20search%20Column%20B%20for%20a%20suffix%20of%20the%20word%20in%20Column%20A%3F%20This%20is%20the%20best%20I%20could%20come%20up%20with%2C%20and%20it%20only%20works%20on%20a%20few%20words%2C%20but%20I%20can't%20figure%20out%20why...%26nbsp%3B%3DVLOOKUP(RIGHT(A2)%2C%24B%243%3A%24B%24561%2C1%2C0).%20I%20would%20really%20appreciate%20any%20help%20you%20can%20provide.%20Thank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1115884%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1115897%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20that%20searches%20for%20suffixes%20to%20a%20word...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1115897%22%20slang%3D%22en-US%22%3E%3CP%3EHere's%20an%20example%20of%20what%20I'm%20looking%20for%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EColumn%20A%3C%2FTD%3E%3CTD%3EResult%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%3CP%3EColumn%20B%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eabactinal%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Eetidine%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eabactinally%3C%2FTD%3E%3CTD%3Eally%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Efacient%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eabaction%3C%2FTD%3E%3CTD%3Etion%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Efaction%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eabactor%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Efashion%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eabaculi%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Eflation%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eabaculus%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Eflorous%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eabacus%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Eglossia%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eabacuses%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Eibility%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eabada%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Eiferous%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eabaddon%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Eiformes%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eabadejo%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Eigerous%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eabadengo%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Eiridine%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eabadia%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Eisation%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eabadite%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Ekinesis%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eabaff%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Elogical%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eabaft%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Emalacia%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eabaisance%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Emanship%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eabaised%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Emeister%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eabaiser%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Emorphic%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eabaisse%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Eography%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eabaissed%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Eolidine%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eabaka%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Eologist%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eabakas%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Eonychia%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eabalation%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Ephrenia%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eabalienate%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Ephyceae%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eabalienated%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Epoiesis%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eabalienating%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Errhagia%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eabalienation%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Errhaphy%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eabalone%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Esterone%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eabalones%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3Etion%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1115909%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20that%20searches%20for%20suffixes%20to%20a%20word...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1115909%22%20slang%3D%22en-US%22%3E%3CP%3EC2%3DIFERROR(LOOKUP(1%2C0%2F(COUNTIF(%24A2%3A%24A2%2C%22*%22%26amp%3B%24B%242%3A%24B%2431)%26gt%3B0)%2C%24B%242%3A%24B%2431)%2C%22%22)%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F165915iA19A6F1ECC9AA300%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Lookup.jpg%22%20title%3D%22Lookup.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1115911%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20that%20searches%20for%20suffixes%20to%20a%20word...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1115911%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F523265%22%20target%3D%22_blank%22%3E%40Grass405%3C%2FA%3E%26nbsp%3BThank%20you%20so%20much%2C%20that%20is%20perfect!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi, I really hope someone can help me with this. I've been searching for hours in multiple forums, but I can't find a solution to my problem.

 

Column A is a list of english words. Column B is a list of english suffixes. How would I search Column B for a suffix of the word in Column A? This is the best I could come up with, and it only works on a few words, but I can't figure out why... =VLOOKUP(RIGHT(A2),$B$3:$B$561,1,0). I would really appreciate any help you can provide. Thank you

3 Replies
Highlighted

Here's an example of what I'm looking for:

 

Column AResult 

Column B

abactinal  etidine
abactinallyally facient
abactiontion faction
abactor  fashion
abaculi  flation
abaculus  florous
abacus  glossia
abacuses  ibility
abada  iferous
abaddon  iformes
abadejo  igerous
abadengo  iridine
abadia  isation
abadite  kinesis
abaff  logical
abaft  malacia
abaisance  manship
abaised  meister
abaiser  morphic
abaisse  ography
abaissed  olidine
abaka  ologist
abakas  onychia
abalation  phrenia
abalienate  phyceae
abalienated  poiesis
abalienating  rrhagia
abalienation  rrhaphy
abalone  sterone
abalones  tion
Highlighted
Solution

C2=IFERROR(LOOKUP(1,0/(COUNTIF($A2:$A2,"*"&$B$2:$B$31)>0),$B$2:$B$31),"")Lookup.jpg

Highlighted

@Grass405 Thank you so much, that is perfect!

Related Conversations
Word 365 svg import problem
Goupil35000 in Office 365 on
0 Replies
Page numbers won't start at 1
jamieschwenger in Office for Mac on
4 Replies
Deleting Sections in Word for the Mac
Bob_Irvine in Office for Mac on
0 Replies
Protected Word forms for assignments
Kylie66 in Word on
0 Replies