Sports Betting

%3CLINGO-SUB%20id%3D%22lingo-sub-2936409%22%20slang%3D%22en-US%22%3ESports%20Betting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2936409%22%20slang%3D%22en-US%22%3E%3CP%20class%3D%22%22%3ESo%20I%20am%20made%20a%20sports%20betting%20model%20(very%20simple)%20and%20its%20more%20so%20just%20for%20fun.%20But%20I%20have%20been%20more%20and%20more%20interested%20in%20improving%20it.%20I%20recently%20started%20using%20power%20ratings%20found%20online%20from%20different%20sources.%3C%2FP%3E%3CP%20class%3D%22%22%3EBasically%20I%20have%204%20different%20sources%20(power%20ratings)%20and%20I%20use%20the%20html%20function%20on%20excel%20to%20get%20them%20into%20my%20workbook.%20I%20then%20use%20a%20drop%20down%20arrow%20and%20Vlookup%20to%20get%20the%20different%20matchups%20projected%20spread%20from%20that%20power%20rating.%20And%20because%20the%20sources%20I%20use%20do%20not%20have%20the%20same%20names%20on%20their%20site%20(unfortunately)%20for%20example%2C%20For%20the%20Charlotte%2049ers%2C%3C%2FP%3E%3CP%20class%3D%22%22%3EOne%20says%20Charlotte%2049ers%2C%20The%20other%20says%20Charlotte%2C%20another%20says%20North%20Carolina%20-%20Charlotte%20and%20the%20other%20says%20Charlotte.%3C%2FP%3E%3CP%20class%3D%22%22%3ESo%20I%20have%20to%20put%20the%20matchup%20in%20four%20separate%20times%20(which%20is%20annoying).%3C%2FP%3E%3CP%20class%3D%22%22%3EI%20was%20wondering%20if%20anyone%20knows%20how%20to%20help%20me%20with%20my%20problem%2C%20so%20I%20only%20have%20to%20put%20the%20matchup%20in%20one%20time%20and%20it%20pulls%20from%20all%20my%20data%20bases.%3C%2FP%3E%3CP%20class%3D%22%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22%22%3EThanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2936409%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2936498%22%20slang%3D%22en-US%22%3ERe%3A%20Sports%20Betting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2936498%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1210234%22%20target%3D%22_blank%22%3E%40windowq%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20assume%20they%20are%20differing%20renderings%20of%20the%20same%20team%20name%3F%3C%2FP%3E%3CP%3EFor%20me%2C%20the%20solution%20would%20to%20be%20to%20conduct%20a%20'wildcard'%20enabled%20search%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20XLOOKUP(%0A%20%20%20%20%22*%22%26amp%3BName%26amp%3B%22*%22%2C%0A%20%20%20%20TeamName%2C%20%0A%20%20%20%20Pts%2C%20%0A%20%20%20%20%22Not%20found%22%2C%0A%20%20%20%202)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Ewhere%20the%20final%202%20introduces%20the%20wildcard%20setting.%26nbsp%3B%20Without%20XLOOKUP%2C%20I%20am%20guessing%3B%20perhaps%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20INDEX(%0A%20%20%20%20%20Pts%2C%20%0A%20%20%20%20%20MATCH(%0A%20%20%20%20%20%20%20%20TRUE%2C%20%0A%20%20%20%20%20%20%20%20ISNUMBER(FIND(Name%2C%20TeamName))%2C%0A%20%20%20%20%200)%0A%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Ebut%20I%20am%20not%20the%20ideal%20person%20to%20answer%20non-365%20questions.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

So I am made a sports betting model (very simple) and its more so just for fun. But I have been more and more interested in improving it. I recently started using power ratings found online from different sources.

Basically I have 4 different sources (power ratings) and I use the html function on excel to get them into my workbook. I then use a drop down arrow and Vlookup to get the different matchups projected spread from that power rating. And because the sources I use do not have the same names on their site (unfortunately) for example, For the Charlotte 49ers,

One says Charlotte 49ers, The other says Charlotte, another says North Carolina - Charlotte and the other says Charlotte.

So I have to put the matchup in four separate times (which is annoying).

I was wondering if anyone knows how to help me with my problem, so I only have to put the matchup in one time and it pulls from all my data bases.

 

Thanks in advance.

5 Replies

@windowq 

I assume they are differing renderings of the same team name?

For me, the solution would to be to conduct a 'wildcard' enabled search

= XLOOKUP(
    "*"&Name&"*",
    TeamName, 
    Pts, 
    "Not found",
    2)

where the final 2 introduces the wildcard setting.  Without XLOOKUP, I am guessing; perhaps

= INDEX(
     Pts, 
     MATCH(
        TRUE, 
        ISNUMBER(FIND(Name, TeamName)),
     0)
  )

but I am not the ideal person to answer non-365 questions.

If only don't care that

 

Charlotte, NC

Charlotte, FL

Charlotte, MI

Charlotte, Canada

 

are different cities.

True; the trick is to provide the minimum number of wrong answers required to define the question. A somewhat archane game of answer and question!

@Peter Bartholomew 

 

What is wildcard?  

Both within XLOOKUP and the xxxIFS family of functions a "?" and a "*" can have special significance matching a single character or multiple characters respectively. That allows a search to be conducted for a substring occurring within the target string.