Feb 23 2021 02:48 AM
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
Feb 23 2021 03:42 AM
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.
Feb 23 2021 03:55 AM