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

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

<tbody>
</tbody>

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