V lookup part of the text string

[thedeadzeds]

Guys, there are 2 tables below. The data table and a lookup table.

Is this possible? I have a list of cars and i want to pull back the brand name. At the moment i am simply adding all the vehicles from the data table into a lookup table and pulling back the brand name from that. However, the vehicles in the veh desc column are never the same so i always have to add all new vehicles to the lookup table.

Is there a way to use a lookup so it pulls in the brand name if the veh desc contains certain words from the lookup table as per below. So for example, row 7 in the data table contains T-ROC. Is there a way to lookup T-roc (rather than the whole description) and pull in the brand form the lookup table. So if it contains T-roc, pull in Volkswagen from the lookup table.

Hope this makes sense.

Data table

Veh Desc	Brand
Ford Transit Custom 310 Trend	Ford
Ford Focus Zetec Tdci	Ford
NULL	NA
Volkswagen Golf Match Ed Bmotion Tsi	Volkswagen
NULL
New Golf S 1.6 TDI 105 PS 5-speed Manual 5 Door	Volkswagen
T-Roc SEL 1.5 TSI 150PS EVO 6-speed Manual 5 Door	Volkswagen
Audi Q3 Sport 2.0 TDI 150 PS 6	Audi
Audi A5 Coup- S line 1.8 TFSI 177 PS 6-speed	Audi
Audi A4 Se Ultra Tdi	Audi
A3 Sportback Sport 2.0 TDI 150 PS 6-speed	Audi
Audi A3 Sportback Sport 2.0 TDI 150 PS 6-speed	Audi
A6 Black Edition 2.0 TDI ultra 190 PS S tronic	Audi
Audi A4 Saloon S line ultra 2.0 TDI 190 PS 6-speed	Audi
Audi A3 E 104 Tdi
Audi A3 Sportback Sport 1.6 TDI 105 PS 5 speed
NULL
Audi A5 Tdi A
Q7 S line 3.0 TDI quattro 272
Q7 S line 3.0 TDI quattro 272
Audi A4 S Line Tfsi Cvt

<colgroup><col><col></colgroup><tbody>
</tbody>

Lookup table

Veh Desc	Brand
Ford	Ford
Volkswagen	Volkswagen
Golf	Volkswagen
T-roc	Volkswagen
A3	Audi
Audi	Audi
Q7	Audi
A3	Audi

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[James006]

Hello,

With your data in range A1:B22 ...

and your lookup Table in D1:E10 ... (by the way ... do not forget to add A6 ...)

In cell B2, you can have following Array formula :

=INDEX($E$1:$E$10,MATCH(TRUE,ISNUMBER(SEARCH($D$1:$D$10, A2)),0))

Hope this will help

 

[thedeadzeds]

Amazing thank you

 

[James006]

You are welcome