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
[TABLE="class: grid, width: 410"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Veh Desc[/TD]
[TD]Brand[/TD]
[/TR]
[TR]
[TD]Ford Transit Custom 310 Trend[/TD]
[TD]Ford[/TD]
[/TR]
[TR]
[TD]Ford Focus Zetec Tdci[/TD]
[TD]Ford[/TD]
[/TR]
[TR]
[TD]NULL[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD]Volkswagen Golf Match Ed Bmotion Tsi[/TD]
[TD]Volkswagen[/TD]
[/TR]
[TR]
[TD]NULL[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]New Golf S 1.6 TDI 105 PS 5-speed Manual 5 Door[/TD]
[TD]Volkswagen[/TD]
[/TR]
[TR]
[TD]T-Roc SEL 1.5 TSI 150PS EVO 6-speed Manual 5 Door[/TD]
[TD]Volkswagen[/TD]
[/TR]
[TR]
[TD]Audi Q3 Sport 2.0 TDI 150 PS 6[/TD]
[TD]Audi[/TD]
[/TR]
[TR]
[TD]Audi A5 Coup- S line 1.8 TFSI 177 PS 6-speed[/TD]
[TD]Audi[/TD]
[/TR]
[TR]
[TD]Audi A4 Se Ultra Tdi[/TD]
[TD]Audi[/TD]
[/TR]
[TR]
[TD]A3 Sportback Sport 2.0 TDI 150 PS 6-speed[/TD]
[TD]Audi[/TD]
[/TR]
[TR]
[TD]Audi A3 Sportback Sport 2.0 TDI 150 PS 6-speed[/TD]
[TD]Audi[/TD]
[/TR]
[TR]
[TD]A6 Black Edition 2.0 TDI ultra 190 PS S tronic[/TD]
[TD]Audi[/TD]
[/TR]
[TR]
[TD]Audi A4 Saloon S line ultra 2.0 TDI 190 PS 6-speed[/TD]
[TD]Audi[/TD]
[/TR]
[TR]
[TD]Audi A3 E 104 Tdi[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Audi A3 Sportback Sport 1.6 TDI 105 PS 5 speed[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]NULL[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Audi A5 Tdi A[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Q7 S line 3.0 TDI quattro 272[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Q7 S line 3.0 TDI quattro 272[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Audi A4 S Line Tfsi Cvt[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Lookup table
[TABLE="class: grid, width: 169"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Veh Desc[/TD]
[TD]Brand[/TD]
[/TR]
[TR]
[TD]Ford[/TD]
[TD]Ford[/TD]
[/TR]
[TR]
[TD]Volkswagen[/TD]
[TD]Volkswagen[/TD]
[/TR]
[TR]
[TD]Golf[/TD]
[TD]Volkswagen[/TD]
[/TR]
[TR]
[TD]T-roc[/TD]
[TD]Volkswagen[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]Audi[/TD]
[/TR]
[TR]
[TD]Audi[/TD]
[TD]Audi[/TD]
[/TR]
[TR]
[TD]Q7[/TD]
[TD]Audi[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]Audi[/TD]
[/TR]
</tbody>[/TABLE]

 

[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