V lookup part of the text string

thedeadzeds

Active Member
Joined
Aug 16, 2011
Messages
314
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 DescBrand
Ford Transit Custom 310 TrendFord
Ford Focus Zetec TdciFord
NULLNA
Volkswagen Golf Match Ed Bmotion TsiVolkswagen
NULL
New Golf S 1.6 TDI 105 PS 5-speed Manual 5 DoorVolkswagen
T-Roc SEL 1.5 TSI 150PS EVO 6-speed Manual 5 DoorVolkswagen
Audi Q3 Sport 2.0 TDI 150 PS 6Audi
Audi A5 Coup- S line 1.8 TFSI 177 PS 6-speedAudi
Audi A4 Se Ultra TdiAudi
A3 Sportback Sport 2.0 TDI 150 PS 6-speedAudi
Audi A3 Sportback Sport 2.0 TDI 150 PS 6-speedAudi
A6 Black Edition 2.0 TDI ultra 190 PS S tronicAudi
Audi A4 Saloon S line ultra 2.0 TDI 190 PS 6-speedAudi
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 DescBrand
FordFord
VolkswagenVolkswagen
GolfVolkswagen
T-rocVolkswagen
A3Audi
AudiAudi
Q7Audi
A3Audi

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

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
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 :

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

Hope this will help
 

Watch MrExcel Video

Forum statistics

Threads
1,122,689
Messages
5,597,559
Members
414,156
Latest member
WDMix

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top