• Hi Guest Just in case you were not aware I wanted to highlight that you can now get a free 7 day trial of Horseracebase here.
    We have a lot of members who are existing users of Horseracebase so help is always available if needed, as well as dedicated section of the fourm here.
    Best Wishes
    AR

Lookup function (or otherwise) HELP! please...

retriever

retriever

Stallion
Hi all,
I wonder if anyone can point me in the direction of the best function/s to use to perform a task in my spreadsheet.

I'm guessing it will involve Lookup, IF or Index-Match functions or combination of any!!??
I cannot seem to think it out :( I don't know if it's a simple task or impossible !!

There are two simple sheets - Main sheet & Draw stats. The idea is to 'lookup' the value for the corresponding Stall no. from 'draw sheet' based on the Track/Distance entry (B3, Main sheet). The stall will obviously change from race to race, as will the Track/Distance data (B3, Main sheet), so I need the correct value to be pulled from 2nd sheet into 1st.

Hopefully, I have made sense and all I need is a pointer, and a 'light bulb' moment, and I can figure the rest out. The attached is just a basic worksheet for a much bigger project.

Thanks in advance.
 

Attachments

  • 7 Runner Race Project.xls
    9 KB · Views: 14
Last edited:
Hi spreadsheetbettor spreadsheetbettor Thank you for your reply.
This is probably a tad more complicated than I have managed to explain.

Not sure what the C7+1 refers too or the !........but that aside......I probably didn't make it clear that in Draw (Stall) numbers (1-7) in column C will be changed from race to race (dynamic) and so, therefore, the corresponding Stall value pulled from Draw stats into column D (main sheet) will shift.

The value I am after for D7, (Main sheet) is the value in D3, (Draw stats) depending upon the value in C7 at that particular moment.

From doing a bit of searching the best I can come up with is possibly using or combining Match and Offset formulas in Vlookups and possibly IF function too.......or somehow breaking the problem into two parts.....but I have no idea if I am on the right track.
 
Last edited:
To add to matters, I use OpenOffice spreadsheets and I had to download a temporary version of Excel in order to attach the spreadsheet to my post.
Ukbettingforum doesn't allow .ods attachments.

NB./ I have just stumbled upon a 30 day free Excel VBA Absolute Beginners course thanks to G gary longstaff
Perhaps I am destined to get 'right in the thick of it'.....lol
 
Last edited:
I am self taught and only average in excel so apologies if my following explanation sucks. 'Draw stats'! means read from the page "draw stats". A2:H11 means read the cells in a rectangle from, a2--> a11 down to H2 -->H11. So 'Draw stats'!A2:H11 says go to "draw stats" sheet and read from the grid there.
Now imagine our grid from A to H as columns with numbers in e.g. A is column 1, B is column 2 etc. In your example, you want draw 3 which can be found in D or column 4 of our grid. Because of the way that you have set the grid out the draw number +1 always matches the column from your grid, so that is where c7+1 comes from.
 
Last edited:
Wow!! thanks, it does work.......I just need to learn why!! Learn and understand the syntax of the formulas.

You even added a drop-down menu for the Track/Distance.....that's cool.....although probably get a bit messy when I have 100+ entries :D
Clever that you re-numbered the 'values', as well, to make it easier to work with - I should have thought of doing that.

I see that the formula now has a $ and not a ! ......I know that $ is used to 'fixed' cell positions.....whether row or column or both.....so does ! fix sheets or was it not needed (or indeed, an error)? I'm confused....lol

Thanks again for helping, and you saying you are self-taught is going to be an inspiration to me going forward (y)

I also see you've posted on the Yesteryear thread. Football obviously, as I see from your avatar, is your game/area.
Have you had much luck betting wise.....correct scores?
 
Last edited:
Primarily football although I am trying to use the hiatus to look at tennis in depth. I chose these sports because of the availability of free data. Correct score wise I have never really tried before but because of the thread on here and one on another forum, I am beginning to look with more interest. Not sure yet if I am going to look at prematch scalping, prematch or in play though.

In terms of excel, I have found that I have collected a load of workbooks and made even more over the years and whenever I get stuck I look at the formulas used there.

Out of interest where do you get the data from for your horse racing?
 
I use HorseRaceBase, like many other users on here, which brought me to this forum.

I have played around with the Systems builder within it, creating systems - or trying too - it's a bit of a learning curve.
Not the software, but the application of it. Rubbish in, rubbish out, as they say. But I think, generally, I am getting there.

I have been in and out of betting over the past 30+ years, mainly horse racing and football, but I have never applied myself to any particular project (or learning). Which is what all the above is about.

If you're interested in horse racing I would say quickly evaluate HRB as there is going to be a price increase for new members from 1st June (see a recent previous post of mine).
www.theukbettingforum.co.uk

Horseracebase

@tacker there are plenty of very good teachers on this site, with @Chesham being just one. I think from all the teachers and the user names that i know of / can remember, @Chesham must be the one person i have related with the longest via various horse racing websites. We all have different...
www.theukbettingforum.co.uk www.theukbettingforum.co.uk

 
Last edited:
Hello retriever retriever , can I add something?

If you want to spread your vlookup across a row put in the b3 as $b3. Also it is safest to use $a$2:$h$11 as this keeps the area you are looking at the same.

Stay well and good luck
 
You must log in or register to reply here.
Back
Top