Spreadsheet: 794732d1662276326-how-to-lookup-the-data-with-wildcard-in-the-middle-samplecase_lookup-with-wildcard-in-the-middle
Sheet: Sample

Field List : Actual data have more fields and value listing (Any = Can show any value but not a condition for lookup) |  |  |  |  |  |  | I created below table for lookup (but it do not work with normal vlookup as there are wildcard in the middle)
Model | Color | Fragrance | Shape | Texture | LOT |  | Index for lookup | Required value from Look Up |  | Samples of extracted Data got from IT | [NAME]? | Expected Result
A-2018 | Gold | Any | Square | Any | 1HF |  |  | X1-0001 |  | A-2018+Gold+Orange+Square+Soft+1HF |  | X1-0001
A-2018 | Gold | Any | Square | Any | 2HF |  |  | X1-0002 |  | [NAME] |  | X1-0001
A-2018 | Gold | Any | Circle | Any | 1HF |  |  | X1-0003 |  | A-2018+Gold+Orange+Square+Soft+2HF |  | X1-0002
A-2018 | Gold | Any | Circle | Any | 2HF |  |  | X1-0004 |  | A-2018+Gold+Orange+Square+Hard+2HF |  | X1-0002
A-2018 | Silver | Any | Square | Any | 1HF |  |  | Y1-0001 |  | A-2018+Gold+Orange+Circle+Soft+1HF |  | X1-0003
A-2018 | Silver | Any | Square | Any | 2HF |  |  | Y1-0002 |  | A-2018+Gold+Orange+Circle+Hard+1HF |  | X1-0003
A-2018 | Silver | Any | Circle | Any | 1HF |  |  | Y1-0003 |  | A-2018+Gold+Orange+Circle+Soft+2HF |  | X1-0004
A-2018 | Silver | Any | Circle | Any | 2HF |  |  | Y1-0004 |  | A-2018+Gold+Orange+Circle+Hard+2HF |  | X1-0004
B-2020 | Gold | Orange | Any | Soft | Any |  |  | X2-0001 |  | A-2018+Gold+Rose+Square+Soft+1HF |  | X1-0001
B-2020 | Gold | Orange | Any | Hard | Any |  |  | X2-0002 |  | A-2018+Gold+Rose+Square+Hard+1HF |  | X1-0001
B-2020 | Gold | Rose | Any | Soft | Any |  |  | X2-0003 |  | A-2018+Gold+Rose+Square+Soft+2HF |  | X1-0002
B-2020 | Gold | Rose | Any | Hard | Any |  |  | X2-0004 |  | A-2018+Gold+Rose+Square+Hard+2HF |  | X1-0002
B-2020 | Gold | Lemon | Any | Soft | Any |  |  | X2-0005 |  | A-2018+Gold+Rose+Circle+Soft+1HF |  | X1-0003
B-2020 | Gold | Lemon | Any | Hard | Any |  |  | X2-0006 |  | A-2018+Gold+Rose+Circle+Hard+1HF |  | X1-0003
B-2020 | Silver | Orange | Any | Soft | Any |  |  | Y2-0001 |  | A-2018+Gold+Rose+Circle+Soft+2HF |  | X1-0004
B-2020 | Silver | Orange | Any | Hard | Any |  |  | Y2-0002 |  | A-2018+Gold+Rose+Circle+Hard+2HF |  | X1-0004
B-2020 | Silver | Rose | Any | Soft | Any |  |  | Y2-0003 |  | A-2018+Silver+Orange+Square+Soft+1HF |  | Y1-0001
B-2020 | Silver | Rose | Any | Hard | Any |  |  | Y2-0004 |  | A-2018+Silver+Orange+Square+Hard+1HF |  | Y1-0001
B-2020 | Silver | Lemon | Any | Soft | Any |  |  | Y2-0005 |  | A-2018+Silver+Orange+Square+Soft+2HF |  | Y1-0002
B-2020 | Silver | Lemon | Any | Hard | Any |  |  | Y2-0006 |  | A-2018+Silver+Orange+Square+Hard+2HF |  | Y1-0002
 |  |  |  |  |  |  |  |  |  | A-2018+Silver+Orange+Circle+Soft+1HF |  | Y1-0003
 |  |  |  |  |  |  |  |  |  | A-2018+Silver+Orange+Circle+Hard+1HF |  | Y1-0003
 |  |  |  |  |  |  |  |  |  | A-2018+Silver+Orange+Circle+Soft+2HF |  | Y1-0004
 |  |  |  |  |  |  |  |  |  | A-2018+Silver+Orange+Circle+Hard+2HF |  | Y1-0004
 |  |  |  |  |  |  |  |  |  | A-2018+Silver+Rose+Square+Soft+1HF |  | Y1-0001
 |  |  |  |  |  |  |  |  |  | A-2018+Silver+Rose+Square+Hard+1HF |  | Y1-0001
 |  |  |  |  |  |  |  |  |  | A-2018+Silver+Rose+Square+Soft+2HF |  | Y1-0002
 |  |  |  |  |  |  |  |  |  | A-2018+Silver+Rose+Square+Hard+2HF |  | Y1-0002
 |  |  |  |  |  |  |  |  |  | A-2018+Silver+Rose+Circle+Soft+1HF |  | Y1-0003
 |  |  |  |  |  |  |  |  |  | A-2018+Silver+Rose+Circle+Hard+1HF |  | Y1-0003
 |  |  |  |  |  |  |  |  |  | A-2018+Silver+Rose+Circle+Soft+2HF |  | Y1-0004
 |  |  |  |  |  |  |  |  |  | A-2018+Silver+Rose+Circle+Hard+2HF |  | Y1-0004
 |  |  |  |  |  |  |  |  |  | B-2020+Gold+Orange+Square+Soft+1QTR |  | X2-0001
 |  |  |  |  |  |  |  |  |  | B-2020+Gold+Orange+Circle+Soft+2QTR |  | X2-0001
 |  |  |  |  |  |  |  |  |  | B-2020+Gold+Orange+Oval+Hard+2QTR |  | X2-0002
 |  |  |  |  |  |  |  |  |  | B-2020+Gold+Rose+Square+Soft+1QTR |  | X2-0003
 |  |  |  |  |  |  |  |  |  | B-2020+Gold+Rose+Oval+Hard+2QTR |  | X2-0004
 |  |  |  |  |  |  |  |  |  | B-2020+Gold+Lemon+Square+Soft+3QTR |  | X2-0005
 |  |  |  |  |  |  |  |  |  | B-2020+Gold+Lemon+Square+Hard+4QTR |  | X2-0006
 |  |  |  |  |  |  |  |  |  | B-2020+SilverOrange+Circle+Soft+1QTR |  | Y2-0001
 |  |  |  |  |  |  |  |  |  | B-2020+SilverOrange+Oval+Soft+2QTR |  | Y2-0001
 |  |  |  |  |  |  |  |  |  | B-2020+SilverOrange+Circle+Hard+1QTR |  | Y2-0002
 |  |  |  |  |  |  |  |  |  | B-2020+SilverOrange+Square+Hard+3QTR |  | Y2-0002
 |  |  |  |  |  |  |  |  |  | B-2020+SilverRose+Circle+Soft+2QTR |  | Y2-0003
 |  |  |  |  |  |  |  |  |  | B-2020+SilverRose+Circle+Hard+2QTR |  | Y2-0004
 |  |  |  |  |  |  |  |  |  | B-2020+SilverLemon+Circle+Soft+3QTR |  | Y2-0005
 |  |  |  |  |  |  |  |  |  | B-2020+SilverLemon+Circle+Hard+4QTR |  | Y2-0006