# similar formula needed to the one I already have

#### jpowell79

##### Active Member
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:

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

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
jpowell79

Untested and a mouthful, but try this:
Code:
``=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``

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?

doesn't seem to do anything Peter

Any other suggestions?

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!

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?

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?

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!

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

thanks very much mate

Replies
7
Views
580
Replies
4
Views
630
Replies
2
Views
315
Replies
6
Views
642
Replies
8
Views
1K

1,221,383
Messages
6,159,539
Members
451,571
Latest member
Qwissy

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back