CODEWITHSUNDEEP
excelexcel-formula
Tresfjording
Tresfjording

Reputation: 1

Index match and wildcards

I have this similar problem. I've been searching the net, but I really can't get anything I find to work.

Cell D43 is containing the string; " [6] Manchester Utd vs Swansea [17] " Cell M11 is containing the string; "Manchester United" Cell N11 is containing the string; "Swansea" Cell H43 is containing a handicapp odds; 1,925 (All references sans quotes) So you see there is a few characters like space and so on that should be left out. That calls for wildcards like "*"&, but ...

There at more than one instance of Manchester Utd in column D, that's why I have to use both team names.

How do I go about to locate Manchester Utd AND Swansea in column D and in cell D43, based on the strings in M11 and N11, and then display the handicap number in H43?

Upvotes: 0

Views: 481

Answers (2)

Kennah
Kennah

Reputation: 487

One approach would be to build an abbreviation lookup.

Add a Worksheet, and call it Abbreviations. In A1 of Abbreviations enter "Manchester United" and in B1 of Abbreviations enter "Manchester Utd".

Back on the main sheet, in O11 enter this formula: =IFERROR(VLOOKUP(M11,Abbreviations!A:B,2,FALSE),M11)

You can copy the formula to rest of Columns O and P. Columns O and P will now give you the abbreviated team names.

Upvotes: 0

tigeravatar
tigeravatar

Reputation: 26640

You are trying to lookup "Manchester United" (M11) against "Manchester Utd" (contained in D43) which will fail a comparison test. If you change the data in cell M11 to match what will be contained in your D column, so that M11 reads "Manchester Utd" then you can use this formula to get the handicap:

=IFERROR(INDEX(H:H,MATCH("*"&M11&"*"&N11&"*",D:D,0)),"Check Spelling")

If the formula cannot find a match, it returns "Check Spelling" to let you know that you may have to change what's in your M and N cells in order to match properly with what is contained in column D.

Upvotes: 1

Related Questions