Diff order list of horses in win & place market issue!

[danjuma]

Need a bit of advice/suggestion guys/gals.

I Have the win market on one sheet, and the equivalent place market on another sheet in same workbook. What I am doing is back dutching two or mores horses in the win market, and sometimes across the place market depending on the odds. Until recently, I thought the order of the horses in the win market was always the same in the place market. IE. horse number 1 in win market, is horse number 1 in place market. Horse number 2 in win market is horse number 2 in place market and so on. So I was using some conditions based on horses in the win market to place bets on horses in the place market, which works fine (bets on the correct horse I want) if the order of the horses in the win market is the same as the place market. However, this goes completely wrong if the order are different, ie. horse number 1 in the win market is horse number 3 in the place market.

What is the best way to get around this please? Many thanks

[osknows]

Hi danjuma,

There are lots of ways to do this in excel. If you have a list of Horses in column A1:A13 and associated data you need to look up in B1:B13 and a revised list of horses in a different order in E1:E13; the following will all return the correct data in column B

=VLOOKUP(E1,$A$1:$B$13,2,FALSE)
=INDEX($A$1:$B$13,MATCH(E1,$A$1:$A$13,0),2)
=OFFSET(B1,MATCH(E1,$A$1:$A$13,0)-ROW(E1),0)

The 1st equation requires data you need to look up being to the right of the lookup data. There are no such restrictions for the other 2 examples

[danjuma]

Hi osknows,

And thanks for your reply. I do understand the concepts of vlookup etc (I think). My problem in this instance is this, if I can explain.

I have a sheet named dutch, in column I, it displays the list of horses from a sheet name Win linked to the win market in BA, and the corresponding back odds in column J. It also display the corresponding place odds in column Q from a sheet named Place linked to the place market in BA. (I have now amended the formula in column Q from originally just =Place!F5 copied down, to =VLOOKUP(I5,Place!$A$5:$F$40,6,0) copied down, after realising the order in the place market is not necessarily the same order in the win market).

Now, as it stands, I have the following formula =IF(Dutch!I6="","",IF(Dutch!AS6=FALSE,"",IF(AND(Dutch!$AR$1=1,Z6=0,Dutch!AX6=1),$W$1,IF(AND(Dutch!$AR$1=2,Dutch!K6="M",Dutch!AX6=1),"CLEAR",IF(AND(Dutch!$AR$1=3,Dutch!K6="M",Dutch!AX6=1),$W$2,IF(AND(Dutch!$AR$1=4,$AA$4<>0,Dutch!AX6=1),$W$3, "")))))) in cell Q5 (trigger column) in the Place sheet, copied down to cell Q40.

Don't worry much about the formula as it's just lots of conditions to be met before placing bet. Now the formula does what I want it to do if the order of the horses in the win and place markets are in sync. However, using an example of recent when not in sync, Sir Boss was horse number 12 in the win market, and Hustle was horse number 11 in the win market. However, they were the other way round in the place market, so instead of the bet being placed on Sir Boss in the place market (after condition met from my parameters in the win market), bet was placed on Hustle.

Where I am having difficulty is how to incorporate the vlookup functions etc in my formula in the trigger column of the place market (or whichever way this can be achieved) so the right horse is bet on in the place market?

Or would I need to do new columns to sort out the order of the horses and corresponding data - odds, stake etc - in the place market to sync with the order in the win market? And if so, how will this work with the Trigger column Q in the place market, if you know what I mean. Thanks.

[Ian]

The principle of having a sheet which brings together the win and place markets is exactly the way that I do it. What you need to do for the triggers is to do a VLOOKUP on the horse' names from the individual win and place sheets to your dutch sheet rather than to a specific cell.

What I would do, is have a column on your dutch sheet which identifies whether a bet is to be triggered or not (YES/NO) and then do VLOOKUPs from your win and place sheets. You might need more than one column if might submit a win but not a place etc.

[osknows]

Ian's suggestion is by far the easiest but here goes....

=IF(VLOOKUP(A6,Dutch!$I$1:$AZ$50,1,FALSE)="","",IF(VLOOKUP(A6,Dutch!$I$1:$AZ$50,37,FALSE)=FALSE,"",IF(AND(VLOOKUP(A6,Dutch!$I$1:$AZ$50,1,FALSE)=1,Z6=0,VLOOKUP(A6,Dutch!$I$1:$AZ$50,42,FALSE)=1),$W$1,IF(AND(Dutch!$AR$1=2,VLOOKUP(A6,Dutch!$I$1:$AZ$50,3,FALSE)="M",VLOOKUP(A6,Dutch!$I$1:$AZ$50,42,FALSE)=1),"CLEAR",IF(AND(Dutch!$AR$1=3,VLOOKUP(A6,Dutch!$I$1:$AZ$50,3,FALSE)="M",VLOOKUP(A6,Dutch!$I$1:$AZ$50,42,FALSE)=1),$W$2,IF(AND(Dutch!$AR$1=4,$AA$4<>0,VLOOKUP(A6,Dutch!$I$1:$AZ$50,42,FALSE)=1),$W$3, ""))))))

copy the above into Q5 and drag down

[danjuma]

Ian's suggestion is by far the easiest but here goes....

=IF(VLOOKUP(A6,Dutch!$I$1:$AZ$50,1,FALSE)="","",IF(VLOOKUP(A6,Dutch!$I$1:$AZ$50,37,FALSE)=FALSE,"",IF(AND(VLOOKUP(A6,Dutch!$I$1:$AZ$50,1,FALSE)=1,Z6=0,VLOOKUP(A6,Dutch!$I$1:$AZ$50,42,FALSE)=1),$W$1,IF(AND(Dutch!$AR$1=2,VLOOKUP(A6,Dutch!$I$1:$AZ$50,3,FALSE)="M",VLOOKUP(A6,Dutch!$I$1:$AZ$50,42,FALSE)=1),"CLEAR",IF(AND(Dutch!$AR$1=3,VLOOKUP(A6,Dutch!$I$1:$AZ$50,3,FALSE)="M",VLOOKUP(A6,Dutch!$I$1:$AZ$50,42,FALSE)=1),$W$2,IF(AND(Dutch!$AR$1=4,$AA$4<>0,VLOOKUP(A6,Dutch!$I$1:$AZ$50,42,FALSE)=1),$W$3, ""))))))

copy the above into Q5 and drag down

GENIUS!!! Never would have figured this out in a million years! Many thanks sir.

[osknows]

It just occured to me that your original formula was in Q5 but references row 6. Without knowing what your formula is doing should this actually go in cell Q6?

[danjuma]

It just occured to me that your original formula was in Q5 but references row 6. Without knowing what your formula is doing should this actually go in cell Q6?

My mistake, I copied the formula in Q6 for my explanation above. I realised this and amended your formula to reference Q5 and copied down. Thanks.