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:

Code:

`=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