Ask Your Question

Vlookup with search item in longer string [closed]

asked 2017-03-22 15:40:03 +0200

toreilly317 gravatar image

updated 2017-03-24 23:56:08 +0200

EasyTrieve gravatar image

I am looking to do something that seems like it would be easy but I just can't seem to figure it out. I need to do a vlookup but i need to search within a longer string but the location may not always be the same place and the length of the text wont always be the same

Description                          | Model
ten people drive a mustang           |
put a v8 in your s10                 |
a civic is a great starter car       |

Data i want to use with vlookip

Mustang | Ford
s10     | Chevy
civic   | Honda

So I want to end up with

Description                          | Model
ten people drive a mustang           | Ford
put a v8 in your s10                 | Chevy
a civic is a great starter car       | Honda
edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-09-28 14:07:37.636512

1 Answer

Sort by » oldest newest most voted

answered 2017-03-22 16:45:11 +0200

karolus gravatar image

updated 2017-03-22 17:13:13 +0200


Try :

=INDEX( B$10:B$13 ; MATCH(1;IFERROR( SEARCH( A$10:A$13; A1)>0;0);0))

entered as Array-formula with keys <ctrl>+<shift>+<enter>

I would describe Ford|Chevy|Honda as vendors instead Model, and Mustang|s10|civic as models → so more descriptive Formula:

=INDEX( vendors ; MATCH( 1 ; IFERROR( SEARCH( models ; description)>0;0);0))
edit flag offensive delete link more

Question Tools



Asked: 2017-03-22 15:40:03 +0200

Seen: 155 times

Last updated: Mar 24 '17