Wrong value returned

I have a text string equation that works very well, but it has a flaw. When I want to find the word college or collège from a range and return collège into another column, it seems to return college when it also finds a part of the word ie. Co as well. How do I fix this?

This is my equation

=IFERROR(LOOKUP(19566,(SEARCH(Varanges18!$C$19:$C$45,V4)),Varanges18!$D$19:$D$45),"")
Keywords and return are arranged into two separate columns


Keyword Return
BAFF Université
Campus Campus
CFMNB Université
Chair Université
Chaire Université
CAN Collège
College Collège
Collège Collège
Collégial Collège
Faculté Université
Faculty Université
Graduate Université
Institut Institut
Institute Institut
Institutes Institut
Instituts Institut
La Cité Collège
NAIT Collège
NSCC Collège
Observatory Université
Observatoire Université
Polytechnic Collège
Polytechnique Collège
Undergraduate Université
Universitaire Université
Université Université
University Université

Hi TMS,

Is this a complicated equation to set up. I got the feel that I am missing something quite minor and yet have spent the night trying to figure it out without success. Would really appreciate help.

See if this works.

=IFERROR(LOOKUP(2,1/SEARCH(" "&Varanges18!$C$19:$C$45&" "," "&SUBSTITUTE(G4,","," ")&" "),Varanges18!$D$19:$D$45),"")

Thank you so much. Will look at this tomorrow morning!

It works really well thank you. It's a totally different formula than I expected. I had thought we would be using wildcards. I would like to understand that formula better. Can you describe it?

Again thank you very much.

Hi Jason,

Thank you very much for the the great equation.

Breaking the formula down a bit for you so you can see how it works.

=IFERROR(LOOKUP(1e+100,SEARCH(" "&Varanges18!$C$19:$C$45&" "," "&SUBSTITUTE(G4,","," ")&" "),Varanges18!$D$19:$D$45),"")

The bit in bold is adding a space to the beginning and end of each keyword in varanges18.
The bit that is underscored adds a space to the beginning and end of the text in G4, it also substitutes commas in G4 with spaces, so that every word in G4 has at least one space before and after. Doing this to look for keywords surrounded by spaces eliminated errors such as "CAN" being incorrectly matched to "Canada"

The other functions remain the same as your original formula, the method of LOOKUP(2,1/ is a habit for eliminating anything that returns a result of FALSE / zero, but it is not needed here, any number will suffice as long as it is greater than the number of characters in column G.

Thank you. I thought it was "*"& at the beginning.

Do I need the 1e+100,SEARCH as your original formula did not include it =IFERROR(LOOKUP(2,1/SEARCH(" "&Varanges18!$C$19:$C$45&" "," "&SUBSTITUTE(G4,","," ")&" "),Varanges18!$D$19:$D$45),"")

The way your equation is written, would it exclude a comma before or after "CAN"?

When you say any number will suffice as long as it is greater than the number of characters in column G ( do you mean rows or the number of characters in the largest cell entry in column G?

I am learning. Thanks again.

"*" is a wildcard character, with the SEARCH function, wildcards are not needed because it does a partial search anyway, by using " " you are looking for an actual space rather than just anything.

1e+100, 2,1/ or 1234 will all do the same in this case, you can use any version. The first is just a very big number (1 followed by 100 0's). 2,1/ converts the results fromn the search to decimals, so the highest number ever found should be 1. 2 us just used as failsafe. With the exception of 2,1/, the numebr just needs to exceed the number of characters in the largest cell.

"CAN" will match up to " CAN " ",CAN " ",CAN," or " CAN," but nothing else, for example " CAN." will not match beacuse of the period after CAN which is not allowed for.

Thanks teach!

Alex