| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match value within any portion of lookup string in range
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: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: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*"). -- Hile |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
match value within any portion of lookup string in range
Hile wrote:
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: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: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&"*" |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
match value within any portion of lookup string in range
Have you tried this?
=VLOOKUP("*"&D1&"*",ApplicationListMaster!C:C,1,0) "Hile" wrote: 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: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: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*"). -- Hile |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
match value within any portion of lookup string in range
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. -- Hile "Sean Timmons" wrote: Have you tried this? =VLOOKUP("*"&D1&"*",ApplicationListMaster!C:C,1,0) "Hile" wrote: 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: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: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*"). -- Hile |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lookup, index, match, range, oh my... | Excel Discussion (Misc queries) | |||
| Formula to return a value for a portion of a text string in a cell | Excel Discussion (Misc queries) | |||
| 2 column lookup - match to date range | Excel Worksheet Functions | |||
| Bold a portion of concatenated string | Excel Discussion (Misc queries) | |||
| Sorting a portion of a range | Excel Worksheet Functions | |||
Linear Mode
