asked on

Excel Vlookup or Index/Match formula to return specific text within a string in cell

Hi! I have cells with long descriptions of products in them. I need to extract the language these products are produced in. I'm hoping to have a formula that can look in column I and see if the words "Spanish", "English", "German","Italian", etc are in the text string and then put the language (ie "Spanish") in column J. I've been trying to research it on the web and I think I need an Index/Match sort of formula, but I can't seem to get it right. I have tried placing a Vlookup table on Sheet2, $A$1:$B$9, but I seem to be getting nowhere. Can anyone help me out? Much thanks!

ASKER CERTIFIED SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

McQMom

ASKER

What is the 2^15 represent?

McQMom

ASKER

Works wonderfully! Thank you!!!

barry houdini

SEARCH function returns a number or an error, if it's a number then that number is the position of the text within the cell, so the largest that number can be is the maximum number of characters you can have in a cell, 32767...or 2 to the power of 15 -1, i.e. 2^15-1. The idea is that the lookup value should be a number greater than any number that SEARCH might return hence 2^15.......in most cases it would probably for be sufficient for that to be some sort of large number, e.g. 9999

When the lookup value is greater than any value in the lookup range then the last match is made. In your case that means that if there were two languages found in I2, e.g. English and Spanish then the formula would return the one listed last in sheet2!A1:A9. Of course I assume that in most cases the text will contain only one language.

regards, barry

McQMom

ASKER

Intertesting! Thanks for the reply...