Possible Index Match Formula Help?

[Celticshadow]

Hello Forum

I have below attached part of my daily race cards that I access each day with a betting forecast below each individual race. What I would like is a formula to match the horses price in the betting forecast to the correct horse in therace card and place it in column K named BFC. Many thanks

Regards

[TABLE="width: 793"]
<tbody>[TR]
[TD]3:05 Exeter[/TD]
[TD="colspan: 10"]Higos Insurance Services Plymstock Handicap Hurdle (Div II) (CLASS 4) (4yo+ 0-110) Winner £3,249 11 runners 2m2f111y[/TD]
[/TR]
[TR]
[TD]NO.[/TD]
[TD]FORM[/TD]
[TD]HORSE[/TD]
[TD]AGE[/TD]
[TD]WGT[/TD]
[TD]TRAINER[/TD]
[TD]JOCKEY[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]BFC[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]66055[/TD]
[TD]Quieto Sol[/TD]
[TD]5[/TD]
[TD]11-12[/TD]
[TD]Charlie Longsdon[/TD]
[TD]Richard Johnson[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]5/1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1687[/TD]
[TD]Grandmaster George[/TD]
[TD]7[/TD]
[TD]11-11[/TD]
[TD]Seamus Mullins[/TD]
[TD]Tom O'Brien[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]10/1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]14-405[/TD]
[TD]Kingussie[/TD]
[TD]8[/TD]
[TD]11-10[/TD]
[TD]Ben Pauling[/TD]
[TD]Nico de Boinville[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]9/2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]824-3F[/TD]
[TD]Lamblord[/TD]
[TD]9[/TD]
[TD]11-10[/TD]
[TD]Robert Walford[/TD]
[TD]Ian Popham[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]11/2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]7-55P3[/TD]
[TD]Lime Street[/TD]
[TD]5[/TD]
[TD]11-7[/TD]
[TD]Tom Symonds[/TD]
[TD]James Davies[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]10/1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2466P/[/TD]
[TD]Superman De La Rue[/TD]
[TD]10[/TD]
[TD]11-7[/TD]
[TD]Mary Evans[/TD]
[TD]Sean Bowen[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]25/1[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]P4P755[/TD]
[TD]Thundering Home14[/TD]
[TD]9[/TD]
[TD]11-6[/TD]
[TD]Richard Mitchell[/TD]
[TD]Tom Bellamy3[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]15/2[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]P/O-14[/TD]
[TD]Sir Dylan[/TD]
[TD]7[/TD]
[TD]11-4[/TD]
[TD]Polly Gundry[/TD]
[TD]Nick Scholfield[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]12/1[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]58999[/TD]
[TD]Brown Bear[/TD]
[TD]5[/TD]
[TD]11-3[/TD]
[TD]Nick Gifford[/TD]
[TD]David Noonan5[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]7/1[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]24-4P4[/TD]
[TD]Kahdian[/TD]
[TD]6[/TD]
[TD]10-11[/TD]
[TD]Helen Rees[/TD]
[TD]Gary Derwin5[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]20/1[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]004P[/TD]
[TD]Beau Knight[/TD]
[TD]4[/TD]
[TD]10-6[/TD]
[TD]Alexandra Dunn[/TD]
[TD]Adam Wedge[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]7/1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 10"]Betting forecast: 9/2 Kingussie, 5/1 Quieto Sol, 11/2 Lamblord, 7/1 Beau Knight, 7/1 Brown Bear, 15/2 Thundering Home, 10/1 Grandmaster George, 10/1 Lime Street, 12/1 Sir Dylan, 20/1 Kahdian, 25/1 Superman De La Rue.[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3:40 Exeter[/TD]
[TD="colspan: 7"]Higos Insurance Services Braunton Novices´ Chase (CLASS 3) (5yo+) Winner £6,498 6 runners 2m3f48y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NO.[/TD]
[TD]FORM[/TD]
[TD]HORSE[/TD]
[TD]AGE[/TD]
[TD]WGT[/TD]
[TD]TRAINER[/TD]
[TD]JOCKEY[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]445-12[/TD]
[TD]Henryville[/TD]
[TD]8[/TD]
[TD]11-3[/TD]
[TD]Harry Fry[/TD]
[TD]Noel Fehily[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]7/4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]213-13[/TD]
[TD]Port Melon[/TD]
[TD]8[/TD]
[TD]11-3[/TD]
[TD]Paul Nicholls[/TD]
[TD]Sam Twiston-Davies[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]9/2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]512P3P[/TD]
[TD]Wizards Bridge[/TD]
[TD]7[/TD]
[TD]11-3[/TD]
[TD]Colin Tizzard[/TD]
[TD]Tom O'Brien[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]14/1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]F6-224[/TD]
[TD]Golden Doyen[/TD]
[TD]5[/TD]
[TD]10-12[/TD]
[TD]Philip Hobbs[/TD]
[TD]Richard Johnson[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]4/1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2-7308[/TD]
[TD]Thomas Crapper[/TD]
[TD]9[/TD]
[TD]10-12[/TD]
[TD]Robin Dickin[/TD]
[TD]Charlie Poste[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]3/1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]9P2-24[/TD]
[TD]Union Saint[/TD]
[TD]8[/TD]
[TD]10-12[/TD]
[TD]Jimmy Frost[/TD]
[TD]Miss B Frost7[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]20/1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 10"]Betting forecast: 7/4 Henryville, 3/1 Thomas Crapper, 4/1 Golden Doyen, 9/2 Port Melon, 14/1 Wizards Bridge, 20/1 Union Saint.[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]






PS I have downloaded and installed HTML maker and watched the video but I can not post a table as set out in the video which is very frustrating as the table in the video looks very impressive, so my apologies for the above table but it is the best I can do with options available.

 

[etaf]

when in excel - what cells are in use for the Betting forecast - are they all in one cell ?
or across various cells

perhaps a sample on a share like onedrive or dropbox may help

 

[Celticshadow]

when in excel - what cells are in use for the Betting forecast - are they all in one cell ?
or across various cells

perhaps a sample on a share like onedrive or dropbox may help

Hi etaf

Many thanks for replying, I will upload the file to dropbox and post a link. Thanks again.

Regards

PS The forecast is one cell.

 

[Celticshadow]

Hi etaf

Here is the file any help you can give would be much appreciated.

Regards

https://www.dropbox.com/s/1s81mg5f6rbxzkm/Book1.xlsm?dl=0

 

[Celticshadow]

Hi Forum

I have found a formula to extact the odds from the forecast cell but it is fixed to the betting forecast price/prices for that race. So how do I fill down to make the formula pick up on the forecast prices for the next race from that races betting forecast cell. I have updated the file and attached a new link, hopeful this will help members to perhaps provide a solution. Many thanks in advance.

Regards

https://www.dropbox.com/s/1s81mg5f6rbxzkm/Book1.xlsm?dl=0

 

[etaf]

i was working on a similar line and had a few issues getting the correct info

BUT to change
=TRIM(RIGHT(SUBSTITUTE(LEFT($A$15,SEARCH(C3,$A$15)-2)," ",REPT(" ",100)),100))

all you need to do is change the formula to
=TRIM(RIGHT(SUBSTITUTE(LEFT($A$27,SEARCH(C20,$A$27)-2)," ",REPT(" ",100)),100))

 

[Celticshadow]

i was working on a similar line and had a few issues getting the correct info

BUT to change
=TRIM(RIGHT(SUBSTITUTE(LEFT($A$15,SEARCH(C3,$A$15)-2)," ",REPT(" ",100)),100))

all you need to do is change the formula to
=TRIM(RIGHT(SUBSTITUTE(LEFT($A$27,SEARCH(C20,$A$27)-2)," ",REPT(" ",100)),100))

Hi etaf

Many thanks for replying. Yep I could do that but as the file is a partial sample I could have upto 50 races per day and editing the formula manually will be very time consuming. What I require is to be able to fill down that column so that it picks up and matches each forecast with each horse in every race.

Regards

 

[AlanY]

try this in K3 and copy down

=IFERROR(TRIM(RIGHT(SUBSTITUTE(LEFT(INDIRECT("$A$"&ROW($A3)+MATCH("Bet"&"*",INDIRECT("A" & ROW()&":A1000"),0)-1),SEARCH(C3,INDIRECT("$A$"&ROW($A3)+MATCH("Bet"&"*",INDIRECT("A" & ROW()&":A1000"),0)-1))-2)," ",REPT(" ",100)),100)),"")

 

[Celticshadow]

try this in K3 and copy down

=IFERROR(TRIM(RIGHT(SUBSTITUTE(LEFT(INDIRECT("$A$"&ROW($A3)+MATCH("Bet"&"*",INDIRECT("A" & ROW()&":A1000"),0)-1),SEARCH(C3,INDIRECT("$A$"&ROW($A3)+MATCH("Bet"&"*",INDIRECT("A" & ROW()&":A1000"),0)-1))-2)," ",REPT(" ",100)),100)),"")

Hi Alan Y

Your solution works an absolute treat, many thanks for your time and effort it is really appreciated. It is unbelievable how knowledgeable and helpful members of this forum are, it really is a super place and I for one am grateful that members such as Alan Y and others are here to help out, a thorough pat on the back to all. Thank you.

Regards

 

[AlanY]

you're welcome