match value within any portion of lookup string in range

[Hile]

WinXP Pro SP3, xls 2k3

I have 2 app lists, neither of which I own, no standardized naming
convention. I need to do an inventory of web apps requiring browser
compatibility testing. I'm trying to compare my final list
(ApplicationListMaster!C:C) with a list dump from a script which queries all
app instances and posts them to a .php page (WebDump!D).

Problem: I can't use wildcards in my formula for the lookup value because
the value is referencing a cell. Or at least I don't know how. And yes,
unfortunately I will like to keep the lists in separate tabs. And sorting
changes so the formula cannot be tied to any specific sorting condition.

Ex.
ApplicationListMaster!C:C
AP Petty Cash
CMS - Collection Management System
e-Credit Memo
eFuel
eFuel Admin
Eram Web
Fax Tracking
Invoice Archive System
LBS - Logistics Billing System
MEPA - Multi-Employee Plan Administration
Vendor Notification
Web Journal Entry
Ad Hoc Finance Reporting Request
Agent Opening Process
Bandolier
Code for Business Conduct
CSR Web Quiz
Delegated Admin
Environmental Self Review
FBI Watch List (Web)
Finance Calendar
Fixed Assets (Web)
Help Desk Online
IT Contact Information
Process Measures
RO Forms
Safety Survey
SSO Registration
Tech Challenge
Bonus
CRG - New Customer Orientation & Vehicle Delivery
Crystal Reports
Customer Rolodex
DTR Report
EBOS - Electronic Bill of Sale
EBS - Email Broadcast System
Insurance Safety Data
PMP Online
Project Estimation Tool
Rental Fuel and Mileage Tax Billing
Sales Lead Web Site
Scanned Contract Documents
SES Online
VSPortal
Web Report Portal
Extranet Setup
Extranet Metrics
Xata Admin Web App
FedEx Rental Admin Application
Fuel Calculator Administration
MappingAdmin
RentalAdmin
Safety Admin
Safety Intranet
BOS - Breach of Security
CDP - Career Development Planning
Crisis Management Application

WebDump!D
bandolier
bonus
cdp
crg
dtr
emetrics
fast
ons
safetyadmin
safetydata
safetyintranet
salesnet
scd

My current formula: =match(D2,ApplicationListMaster!$C:$C,0) works when
there's an exact match (i.e. bandolier returns value "15", but cdp which
should return value "56" returns #N/A instead)

So how do I enter the formula, so that it will look up any match of *cdp*
within App list range. It won't let me use the (*) next to the cell reference
*D2* like I would be able to use if I were writing a text string ("*cdp*").

 

[Glenn]

WinXP Pro SP3, xls 2k3

I have 2 app lists, neither of which I own, no standardized naming
convention. I need to do an inventory of web apps requiring browser
compatibility testing. I'm trying to compare my final list
(ApplicationListMaster!C:C) with a list dump from a script which queries all
app instances and posts them to a .php page (WebDump!D).

Problem: I can't use wildcards in my formula for the lookup value because
the value is referencing a cell. Or at least I don't know how. And yes,
unfortunately I will like to keep the lists in separate tabs. And sorting
changes so the formula cannot be tied to any specific sorting condition.

Ex.
ApplicationListMaster!C:C
AP Petty Cash
CMS - Collection Management System
e-Credit Memo
eFuel
eFuel Admin
Eram Web
Fax Tracking
Invoice Archive System
LBS - Logistics Billing System
MEPA - Multi-Employee Plan Administration
Vendor Notification
Web Journal Entry
Ad Hoc Finance Reporting Request
Agent Opening Process
Bandolier
Code for Business Conduct
CSR Web Quiz
Delegated Admin
Environmental Self Review
FBI Watch List (Web)
Finance Calendar
Fixed Assets (Web)
Help Desk Online
IT Contact Information
Process Measures
RO Forms
Safety Survey
SSO Registration
Tech Challenge
Bonus
CRG - New Customer Orientation & Vehicle Delivery
Crystal Reports
Customer Rolodex
DTR Report
EBOS - Electronic Bill of Sale
EBS - Email Broadcast System
Insurance Safety Data
PMP Online
Project Estimation Tool
Rental Fuel and Mileage Tax Billing
Sales Lead Web Site
Scanned Contract Documents
SES Online
VSPortal
Web Report Portal
Extranet Setup
Extranet Metrics
Xata Admin Web App
FedEx Rental Admin Application
Fuel Calculator Administration
MappingAdmin
RentalAdmin
Safety Admin
Safety Intranet
BOS - Breach of Security
CDP - Career Development Planning
Crisis Management Application

WebDump!D
bandolier
bonus
cdp
crg
dtr
emetrics
fast
ons
safetyadmin
safetydata
safetyintranet
salesnet
scd

My current formula: =match(D2,ApplicationListMaster!$C:$C,0) works when
there's an exact match (i.e. bandolier returns value "15", but cdp which
should return value "56" returns #N/A instead)

So how do I enter the formula, so that it will look up any match of *cdp*
within App list range. It won't let me use the (*) next to the cell reference
*D2* like I would be able to use if I were writing a text string ("*cdp*").

Try "*"&D2&"*"

 

[Sean Timmons]

Have you tried this?

=VLOOKUP("*"&D1&"*",ApplicationListMaster!C:C,1,0)

 

[Hile]

I thought for some reason that with vlookups the data had to be on the same
sheet. I haven't used that in a while. Thanks. Also I never think of the "&"
symbol, I always forget it.

Adding the "&" worked with my match formula, but I still like yours better
because it brought back the whole text string which I now realize is more
useful since I can quickly review whether or not it picked up the right app
from the other tab.

Thanks a lot. Have a great weekend.