Wild Card Vlookup Table Array

[delpiero5464]

I've got a column (I:I) that contains a string of text. I need to lookup a shorter string from a different sheet/table within I2. For instance, I2 = 14FS UL Primary 2015-16. My table (A:B) has A = 14FS UL B = Full. I would like the lookup formula in J2 to bring in "Full".

I've tried using: =VLOOKUP(I2,"*"&$A$2:$B$21&"*",2,FALSE), but that errors. Is there a way to put wild cards around the values in the table array?

Thanks in advance for any help.

 

[circledchicken]

Hi and welcome to the forum,

Perhaps try something like this, using CTRL-SHIFT-ENTER rather than just ENTER as this is an array formula:

=LOOKUP(
    2,
    1 / IF(A$2:A$21 <> "", SEARCH(A$2:A$21, I2)),
    B$2:B$21)

 

[delpiero5464]

Thanks circlechicked. That worked great. Would you mind explain the different components of this formula and what they do? Also, what's the difference between ctrl-shift-enter vs. just enter?

Thanks for the help!

 

[circledchicken]

You're welcome.

The main components of this type of formula are explained in detail by Aladin Akyurek here:
http://www.mrexcel.com/forum/excel-questions/99621-lookup-value-unsorted-data.html#post492425

For CTRL-SHIFT-ENTER, the following links may help introduce the concept:
Array Formulas
http://www.mrexcel.com/articles/CSE-array-formulas-excel.php