Looking for a formula to find the first cell above that contains 2 values and then obtain the value in the cell to the right

[marshen]

Morning all.

Thank you for your help on previous questions.

Today I'm looking at horse racing probability and need a little help please.

In my screenshot below I already have formulas on row 136 for columns B-F and for assistance i've put the corresponding formula in the row above.

I want to replace the formula in column B so it does a check of the value in column A first and if the cell in column A contains the word "Places" AND "/" then I want the formula to look upwards to find the first cell that contains the word "runners" AND ":", then return the value in the cell to the right.

So in effect, the new formula that would go in B137 would recognise that A137 contains "Places" AND "/" and therefore would look up and find that A136 contains "runners" AND ":" and therefore returns the value in B136, so in this example the value I would get in B137 would be 17.


I've done some testing and know that the formula below will return the value 1 because cell A137 contains "Places" AND "/", however I want the solution mentioned above to go in the place of 1.

As you will notice in the formula below, if A137 doesn't contain "Places" AND "/" then the formula reverts to the original =(LEFT(A137,FIND(" ",A137)-1)) formula.

=IF(AND(ISNUMBER(SEARCH("Places", A137)), ISNUMBER(SEARCH("/", A137))), 1,(LEFT(A137,FIND(" ",A137)-1)))

Hopefully someone can help me sort the missing piece of my puzzle.

Any questions please ask.

Thanks
Mark

 

[DanteAmor]

Try this:

Dante Amor
	A	B
1	1	Runners
2
135	16:20 Doncaster	16:20
136	17 runners - Standard places: 4	17
137	6 places 1/5	17
138	SkyBet (min. 16)	SkyBet
139	20 runners - Standard places: 2	20
140	8 places 2/5	20
Hoja1

Cell Formulas
Range	Formula
B135:B140	B135	=IFERROR(IF(ISNUMBER(SEARCH("places*/",A135)),INDEX($B$2:B134,LARGE((IF(ISNUMBER(SEARCH("runners*:",$A$2:A134)),ROW($A$2:A134)))-ROW($B$2)+1,1)),LEFT(A135,FIND(" ",A135)-1)),LEFT(A135,FIND(" ",A135)-1))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

IF you have the XLOOKUP function, you could try this

23 01 29.xlsm
	A	B
135	16:20 Doncaster	16:20
136	17 runners - Standard places: 4	17
137	6 places 1/5	17
138	SkyBet (min. 16)	SkyBet
139	5 places 1/5	17
Lookup

Cell Formulas
Range	Formula
B135:B139	B135	=IF(COUNTIF(A135,"*places*/*"),XLOOKUP("*runners*:*",A$1:A134,B$1:B134,"",2,-1),LEFT(A135,FIND(" ",A135)-1))

 

[Peter_SSs]

IF you have the XLOOKUP function, you could try this

If you don't have XLOOKUP, you could try this one

23 01 29.xlsm
	A	B
135	16:20 Doncaster	16:20
136	17 runners - Standard places: 4	17
137	6 places 1/5	17
138	SkyBet (min. 16)	SkyBet
139	5 places 1/5	17
Lookup (2)

Cell Formulas
Range	Formula
B135:B139	B135	=IF(COUNTIF(A135,"*places*/*"),INDEX(B:B,AGGREGATE(14,6,ROW(B$1:B134)/SEARCH("*runners*:*",A$1:A134),1)),LEFT(A135,FIND(" ",A135)-1))

 

[marshen]

Try this:

Dante Amor
	A	B
1	1	Runners
2
135	16:20 Doncaster	16:20
136	17 runners - Standard places: 4	17
137	6 places 1/5	17
138	SkyBet (min. 16)	SkyBet
139	20 runners - Standard places: 2	20
140	8 places 2/5	20
Hoja1

Cell Formulas
Range	Formula
B135:B140	B135	=IFERROR(IF(ISNUMBER(SEARCH("places*/",A135)),INDEX($B$2:B134,LARGE((IF(ISNUMBER(SEARCH("runners*:",$A$2:A134)),ROW($A$2:A134)))-ROW($B$2)+1,1)),LEFT(A135,FIND(" ",A135)-1)),LEFT(A135,FIND(" ",A135)-1))
Press CTRL+SHIFT+ENTER to enter array formulas.

Thank you for your response.
Your formula works with the same test which is great.
I didn't want to complicate the initial request, however I'm wanting to apply the same logic across columns B & C & D.

So the final outcome would look like the below.

I have some conditional formatting that is making the cells blank on rows 137 & 138, this is based on column F having an error.
So with your great formula I'd get 17 in B139 which is great, however I'm wanting to of course get 4 in C139 and 5 in D139.

I'm looking at the formula and getting a little lost in it, are you able to provide me a solution for columns C&D? I'm sure it's just a slight tweak but it's a little complex for.

For reference i'm on 365.

Thanks
Mark

 

[marshen]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

IF you have the XLOOKUP function, you could try this

23 01 29.xlsm
	A	B
135	16:20 Doncaster	16:20
136	17 runners - Standard places: 4	17
137	6 places 1/5	17
138	SkyBet (min. 16)	SkyBet
139	5 places 1/5	17
Lookup

Cell Formulas
Range	Formula
B135:B139	B135	=IF(COUNTIF(A135,"*places*/*"),XLOOKUP("*runners*:*",A$1:A134,B$1:B134,"",2,-1),LEFT(A135,FIND(" ",A135)-1))

Thank you for your reply.
I'm on 365 and I have updated the details.

I've just posted another reply below, hopefully someone can help me get the desired final outcome.

Thanks
Mark

 

[Peter_SSs]

I'm on 365 and I have updated the details.

Thanks for updating.

So, did you try the XLOOKUP formula?

however I'm wanting to of course get .... 5 in D139.

Why is that "of course"? Where does the 5 come from?

 

[Peter_SSs]

Also, you are describing what you want in row 139 but you are not describing how the values in any other row (eg row 136) are derived.

 

[marshen]

Thanks for updating.

So, did you try the XLOOKUP formula?


Why is that "of course"? Where does the 5 come from?

Yes I've just tested the XLOOKUP and the results are below

To make it easier I've highlighted in yellow what i'm interested in, everything else in columns B-F will be hidden.

So I'm now looking for 2 formulas 1st to get 4 in C136 & C139 and the 2nd to get 6 in D136 & 5 in D139.

Column C extracts the number of standard places, as seen in A136 for that particular race.
Column D extracts the number of places that the bookmaker is paying for that particular race.
In this example, Skybet are paying 6 as seen in A137 and then other bookmakers are paying 5 places, as seen in A139.

The reason I originally requested finding the first row above is because there are many races on the same sheet and the calculations need to be done on a race by race basis.
Hopefully the 2nd screenshot below shows where the data is coming from, the colour coding should help.

Any help appreciated.

Thanks
Mark

 

[Peter_SSs]

Could you provide that sample data with XL2BB so that we can copy for testing rather than manually typing everything out (with likely errors)?