google spreadsheet - reference cell via two partial matches on another cell -

i need match 2 conditions on cell name , add price information cell price if both condition match. in other words, if name contains both conditions, price. tried different approaches using query, search; find; vlookup got stuck somewhere in middle. here's example sheet (google spreadsheet solution preferred on excel):

https://docs.google.com/spreadsheets/d/1zwg3_5ctg_iz1ki04uee-qivmrnq4gmewysmycmklfa/edit?usp=sharing

maybe important: both, name values whole reference table pulled other files dynamically. don't know concerning order or length of these columns in advance, not if there matches @ all.

in addition previous answer , given current set of data (in google spreadsheet), in b2 try:

=arrayformula(iferror(vlookup(regexreplace(a2:a; "[^a-z]"; ""); {e2:e&f2:f\g2:g}; 2; 0))) 

and see if works ?