Ask Your Question
0

Vlookup with search item in longer string

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 close merge delete

1 Answer

Sort by » oldest newest most voted
0

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

karolus gravatar image

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

Hallo

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
Login/Signup to Answer

Question Tools

2 followers

Stats

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

Seen: 84 times

Last updated: Mar 24 '17