similar formula needed to the one I already have

jpowell79

Active Member
Joined
Apr 20, 2006
Messages
331
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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,645
Office Version
  1. 365
Platform
  1. Windows
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
 

jpowell79

Active Member
Joined
Apr 20, 2006
Messages
331
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?
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,726

ADVERTISEMENT

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

Active Member
Joined
Apr 20, 2006
Messages
331
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

Active Member
Joined
Apr 20, 2006
Messages
331

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,726
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

Active Member
Joined
Apr 20, 2006
Messages
331
Domenic your a legend! :) .....this isn't the first time you've helped me out

thanks very much mate :)
 

Forum statistics

Threads
1,141,626
Messages
5,707,486
Members
421,510
Latest member
haroonstr

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top