Analysing BSP horse racing results

[hughsiepops]

Hi

Not directly a BA question, but something that I was at to then implement into BA if it proves profitable. I was looking for a little help in analysing some BSP horse racing results.

I've uploaded the file I'm trying to work through to dropbox here ..
https://www.dropbox.com/s/m0ru7o284n7rq ... .xlsx?dl=0

The Results are held in two separate worksheet (BF_Results and BF_Results_Place)

I think I need a combination of the INDEX, MATCH and IF functions, but after several hours trying I'm struggling to work this out myself and was looking for some help / guidance

Where I want to get to is a point where on the first Worksheet (worksheet THREE) ..

Column A : Date. There is always three selections per day
Column B : Time. This is the time of the race
Column C : Rank. This is the rank of the runner in the race (I would like to be able to manually change this to look at the 1st favourite, 2nd favourite, etc .. in that particular date / Time of race)
Column D: Selection. The name of the horse (Taken from Column F on the BF_Results worksheet) based on the Date, Time and Rank entered in Columns A to C - This is what I am unable to pull through using the information in the Date, Time and Rank columns
Column E : BF_Win. This is the Odds of the horse in the race (Taken from Column H on the BF_Results worksheet based on the Date and Selection / Horse (Since a horse will only run once per day). I've been able to populate this information
Column F : W/L. Whether the horse Won or Lost (Taken from Column Z on the BF_Results worksheet) and based on the Date and Selection. I've been able to populate this information
Column H : Same as Column D
Column I : BF_Place. This is the Odds of the horse in the race (Taken from Column H on the BF_Results worksheet based on the Date and Selection / Horse. I've been able to populate this information
Column J : W/L. Whether the horse Won or Lost (Taken from Column Z on the BF_Results_Place worksheet). I've been able to populate this information

I would like to get to a point where I simply enter the Date, Time and Rank in Columns A, B and C and Columns D through to J are auto-populated based on looking up the information in Worksheets BF_Results and BF_Results_Place.

I've managed to pull through the Odds and W/L information for each selection, but I've not been able to pull through the Selection nor integrate cross referencing the Rank

Worksheet THREE has the results for January 2022 manually populated (It took a while to do this but I simply hit a brick wall when trying to automate this using Excel myself)

Any help / guidance would be much appreciated

Thanks
Richard

[NorthView]

I'd post this on an Excel forum rather than here, though you might get help here.

[alrodopial]

I guess this should do the job:

https://exceljet.net/formula/index-and- ... e-criteria

It's an array formula

[hughsiepops]

Cheers alrodopial - This definitely helps, and NorthView - Think you might be right. It is more of an Excel question than a BA question. Will post on an Excel forum

Thanks
Richard

[alrodopial]

In sheet "Three" create a column with the formula in ie K2=A2+B2 copied down
In sheet "Three", cell C2 type the formula and press control+shift+enter:

=INDEX(BF_Results!$F$2:$F$250,MATCH(1,(BF_Results!$D$2:$D$250=K2)*(BF_Results!$T$2:$T$250=C2),0))

change the "250" in the formula as you like as this will be the last row with BF data in BF_Result sheet
copy down the formula

[hughsiepops]

That's great, thanks alrodopial