Wildcard lookup

[MLK]

I'm not sure if this is possible with excel functions (and I'm only a
beginner at VBA).

NOTE: I did post this question earlier but I did not word it clearly plus I
am including an example.

I have a workbook with 2 tabs, the first tab call "applications" lists all
the applications our business has (ie 400 applications) and the second tab
called "projects" contains a list of all projects for the entire year for the
applications (ie 1000 projects).

The application name from the Applications tab will be "embedded" within the
project name on the Projects tab. Note: there will often be more than 1
project on the go for any given application.

On the Projects tab, I would like to create a new column that can identify
which application it is for by somehow being able to compare the application
names on the Applications tab to the project names on the Projects tabl.

The problem is trying to use wildcards without actually typing the wildcard
itself into the function. I've trying using vlookup, Index, match and
search without luck.

For example, there may be 3 projects on the go for an application called ELM
but the word ELM could be found anywhere in a project name. In the new
column, I would need to identify all 3 projects and so on for all the other
applications.


Here is a mockup of the Applications tab:

APPLICATION............ Year......... Owner

BIF...........................2007......... John Smith
BLAN........................2007......... Mary Smith
C3............................2007.........Pete Smith
CBC.........................2007......... Jerry Smith
CCO.........................2007......... Abby Smith
ELM..........................2007......... Joe Smith
LOANS......................2007......... Jane Smith


Here is a mockup of the Projects tab with the list of projects (and a new
column that needs the application to be populated from the first tab):

PROJECT NAME..........................PROJECT MANAGER.....APPLICATION

TEST ELM PRINTING PROBLEM.... John Doe......................?
CICS SUPPORT..........................Jane Doe......................?
NEW CBC FORMS PROCESSES.... Mary Doe......................?
ELM BATCH ENHANCEMENTS...... Joe Doe........................?
S/E FOR DIY BUSINESS..............Jim Doe........................?
SOFTWARE UPGRADE FOR ELM... Janet Doe.....................?
CBC SUPPORT...........................Lil Doe.........................?
MISC TASKS FOR JIF..................Paul Doe......................?

 

[Max]

Source matching list is assumed in sheet: Applications, within A2:A8

In sheet: Projects,

Project names are running in A2 down

Put in C2, then array-enter the formula, ie press CTRL+SHIFT+ENTER
instead of just pressing ENTER
=IF(ISNA(MATCH(TRUE,ISNUMBER(SEARCH(Applications!$A$2:$A$8,A2)),0)),"",INDEX(Applications!$A$2:$A$8,MATCH(TRUE,ISNUMBER(SEARCH(Applications!$A$2:$A$8,A2)),0)))
Copy C2 down as far as required

Replace SEARCH with FIND in the expression if you want it stricter,
case-sensitive. SEARCH is not case sensitive.

 

[MLK]

Thanks Max! You are the best! This works perfectly. I have struggled for
days on this.

Much appreciated.

Thanks again, Mary-Lou

 

[Max]

Glad it helped, Mary-Lou.
Thanks for the feedback

 

[daddylonglegs]

You could also do this with a non CSE formula

=IF(SUMPRODUCT(1-ISERR(SEARCH(applications!A$2:A$8,A2))),LOOKUP(2^15,FIND(applications!A$2:A$8,A2),applications!A$2:A$8),"")

 

[daddylonglegs]

.....note I mixed and matched FIND and SEARCH in the above, it should be
either FIND twice or SEARCH twice.......