similar formula needed to the one I already have

[jpowell79]

Hi Guys,

I have a formula which works really well for what it needs to do, but I'd love to have a similar formula which performs a slightly different funtion

Here is the formula I already have:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,LOOKUP(2,1/('sheet2'!H3:H9999="positive!"),'sheet2'!f3:f9999)))

This basically looks at the lowest instance of "positive" in the H column, then grabs the value from the adjacent F column


What I'd like is a similar formula which looks at the lowest instance of "positive" in H column, then takes the value from adjacent G column AFTER the 8th character AND until there is a space!

I think I need to offer an example here:

lets say H9="positive"

then lookup G9...

G9 = passing 14.2341 USD monday

So I would be looking for: 14.2341 as this starts at the 8th character and finishes as soon as there is a space (before USD)

The value I'm looking for always starts at the 8th character (as all entries in column G start with "passing") but the value can vary so needs to be recorded up to the space.


Another example:

H12 = "positive"

G12 = passing 4.49 USD wednesday

so I would be looking for 4.49


Hope this makes sense!

Thanks

 

[Peter_SSs]

jpowell79

Untested and a mouthful, but try this:

=MID(LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,LOOKUP(2,1/('list of events'!H3:H9999="positive!"),'sheet2'!G3:G9999))),9,FIND(" ",LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,LOOKUP(2,1/('list of events'!H3:H9999="positive!"),'sheet2'!G3:G9999))),10)-9)+0

 

[jpowell79]

hi Peter,

many thanks..... I've just edited my original posting as the formula I added was wrong....have changed the posting now to the correct formula...not sure if it makes any difference?

 

[jpowell79]

doesn't seem to do anything Peter

Any other suggestions?

 

[Domenic]

Try...

=LOOKUP(9.99999999999999E+307,--MID(LOOKUP(2,1/(Sheet2!H3:H9999="positive!"),Sheet2!F3:F9999),8,ROW(INDEX(A:A,1):INDEX(A:A,1024))))

Or, if you prefer...

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,LOOKUP(9.99999999999999E+307,--MID(LOOKUP(2,1/(Sheet2!H3:H9999="positive!"),Sheet2!F3:F9999),8,ROW(INDEX(A:A,1):INDEX(A:A,1024))))))

...which will return zero when there's no match.

Hope this helps!

 

[jpowell79]

Hi Domenic,

I've tried both of these but no luck I'm afraid

I'm a bit confused as to why you'd reference column A in the formulas?

 

[jpowell79]

Just spotted why it wasn't working.... the formulas you posted were referencing column F instead of column G...I changed the letters and now it works

Thanks Domenic

Still curious as to why it references column A though? Or am I just being thick?

 

[Domenic]

Just spotted why it wasn't working.... the formulas you posted were referencing column F instead of column G...I changed the letters and now it works

I was looking at your original post in which I thought the formula was corrected, as per...

I've just edited my original posting as the formula I added was wrong....have changed the posting now to the correct formula..

Thanks Domenic

You're very welcome!

Still curious as to why it references column A though? Or am I just being thick?

The formula references Column A in order to help return an array of numbers. Both INDEX formulas point to a cell, and together they reference a range. So, for example, here's how the ROW(INDEX():INDEX()) part of the formula is evaluated...

ROW(INDEX(A:A,1):INDEX(A:A,1024))

ROW(A1:A1024)

{1;2;3;4;5...1024}

Hope this helps!

 

[jpowell79]

Domenic your a legend! .....this isn't the first time you've helped me out

thanks very much mate