VLOOKUP incomplete match

[BakerBaker]

I have been lucky to have received help previously on this board and would appreciate some help with the following:

I am preparing a football (soccer) prediction model with data from 2 different sources. Unfortunately, there are variations in the naming of some of the teams (eg Man Utd and Manchester Utd) which is causing a problem with the "normal" use of the VLOOKUP formula.

In the below, I wish to populate the yellow cell on sheet 1 with the data in the blue cell on sheet 2 - Please note the non-matching name variation for "Bristol".

Thanks is anticipation

 

[abssorb]

You can use wildcards in vlookups

So you can use A21&"*" which would match Bristol with anything after

How to use Wildcards with VLOOKUP in Excel (Formula)

By using wildcards with VLOOKUP you can win all those situations where it's hard to use VLOOKUP. It will help you look up a value...

excelchamps.com

 

[BakerBaker]

Thanks, but that doesn't match Bristol CITY with just Bristol in that order. The formula is to be entered in the yellow cell, not vice versa.

 

[abssorb]

I assumed that way around because the other way doesn't make sense. We can't search a longer string to match a shorter one.

To shorten your search term, just get the text left of the first space with; LEFT(A5, FIND(” “, A5) -1)

Vlookup First Word In Excel

vlook formula first word in a string.first word in a string vlookup.vlookup from first word in a sentence.lookup value of first word in a string in excel.excel vlookup first word.

exceltutorialworld.com

Or you could add a hidden helper column.

 

[jtakw]

Hi,

Use this in B5, change adjust range/cell references to suit your data:

Book3.xlsx
	A	B			E	F
5	Bristol City	1.23
6
20
21	Bristol					1.23
Sheet861

Cell Formulas
Range	Formula
B5	B5	=LOOKUP(2,1/SEARCH(" "&A$21:A$30&" "," "&A5&" "),F$21:F$30)

 

[Fluff]

@BakerBaker
How do you know if that's City or Rovers in the bottom list?

 

[BakerBaker]

@BakerBaker
How do you know if that's City or Rovers in the bottom list?

It's league dependent (which could be a problem for the 2 Manchesters!)

 

[BakerBaker]

Hi,

Use this in B5, change adjust range/cell references to suit your data:

Book3.xlsx
	A	B			E	F
5	Bristol City	1.23
6
20
21	Bristol					1.23
Sheet861

Cell Formulas
Range	Formula
B5	B5	=LOOKUP(2,1/SEARCH(" "&A$21:A$30&" "," "&A5&" "),F$21:F$30)

Thanks for this will adapt the sheets/cells/rows etc.