Lookup Contains

[MikeWilk11]

Hi All, In the attached excel file I have a small sample of data that I am trying to get the lookup value to automatically update the sport and event columns for the different programmes.

I tried a Vlookup with wildcards but as my lookup values contained less text than the data I am looking up against it did not work.

Is there a way to get this lookup to work, so if it contains some of the text in the lookup value column it can assign this to the relevant programme? So for example based on the 'La Liga' lookup value

'La Liga - Real Madrid v Barcelona' would have sport = football, event = La Liga

Any advice or help would be great. Thanks, Mike

[#2]

@MikeWilk11 

In B2 as an array formula confirmed with Ctrl+Shift+Enter:

=INDEX($B$13:$B$16,MATCH(TRUE,ISNUMBER(SEARCH($A$13:$A$16,A2)),0))

and in C2, also confirmed with Ctrl+Shift+Enter:

=INDEX($C$13:$C$16,MATCH(TRUE,ISNUMBER(SEARCH($A$13:$A$16,A2)),0))

Fill or copy down.

[#3]

Hi Hans you are a genius, that is exactly what i was after. Thanks for your help with this, much appreciated.