find everything to the left of (multiple input)

Question

My files

Workbook 1 containing Laptops and Inventory sheets, inventory will grab data from laptops taking only the laptop names.

Objective

Extract product names (10,000 entries) from the sheet Laptops e.g. Compaq CQ58-250SA

Plan

We only need the product names so find certain keywords (computer specifications) and take everything to its left (name of the product)

Keywords to target

• Celeron
• Pentium
• B815
• E1-1200
• B820

Spreadsheet Data

• Cell A1 = Compaq CQ58-250SA Celeron B830, Windows

• Cell A2 = HP 650 Pentium B980, 15.6 HD AG LED SVA,

• Cell A3 = Asus X401A-WX321H, B815, Windows 8

• Cell A4 = Compaq CQ58-256SA E1-1200, Windows 8 64

• Cell A5 = ASUS, X401A-WX089V, B820, Windows 7

What I have done so far:

=LEFT(Laptops!A1,FIND("Celeron",Laptops!A1)-1)

What I need to do

=LEFT(Laptops!A1,FIND("Celeron"&"Pentium"&"B815,Laptops!A1)-1)

Answer 1

If your list of fruits is in D1:D3 you could use this:

=LEFT(A1,FIND(INDEX(D1:D3,MATCH(1,COUNTIF(A1,"*"&D1:D3&"*"),0)),A1)-1)

It needs to be entered as an array formula, by pressing CTRL+SHIFT+ENTER.