AGAIN... I need another Lookup Function to extract some data
 

Thanks to Biff, I found 1/2 of what I need. I thought I could figure out how to plug in the Offset formula to his solution to give me what I want so far, but no luck. So I need another formula to do the following:

I have is a list like this:
A B
1 1.10

2 1.20

3 1.31

4 1.40

5 1.49

6 1.57

7 1.65

8 1.70

9 1.74

10 1.75

11 1.73

12 1.66

13 1.58

14 1.49

15 1.41

I want "2" VALUES

Value #1 ( Using the value in Column B located above "peak" value 1.74 )
I want value in Column A adjacent to the highest value < 1.6 (which is 1.57)
I want this returned ( #6 ) which is adjacent to 1.57

Value #2 ( Using the value in Column B located below "peak" value 1.74 )
I want value in Column A adjacent to the highest value < 1.6 (which is 1.58)
I want this returned ( #13 ) which is adjacent to 1.58

================================================== ============
These are the formulas Biff gave me to extract values:
For V1:
Entered as an array using the key combination of CTRL,SHIFT,ENTER:

=MAX(IF(B1:INDEX(B1:B15,MATCH(MAX(B1:B15),B1:B15,0 ))<C1,B1:INDEX(B1:B15,MATCH(MAX(B1:B15),B1:B15,0)) ))

For V2: (array entered)
=MAX(IF(INDEX(B1:B15,MATCH(MAX(B1:B15),B1:B15,0)): B15<C1,INDEX(B1:B15,MATCH(MAX(B1:B15),B1:B15,0)):B 15))

PS. I don't want the hash mark in the format.. I just want the numbers 6 and 13.
Thanks,
BillReese

AGAIN... I need another Lookup Function to extract some data
 

Assume:

F1 = V1 = 1.57
F2 = V2 = 1.58

For V1 (array entered):

=INDEX(A1:A15,MATCH(F1,B1:INDEX(B1:B15,MATCH(MAX(B 1:B15),B1:B15,0)),0))

For V2 (array entered):

=INDEX(A15:INDEX(A1:A15,MATCH(MAX(B1:B15),B1:B15,0 )),MATCH(F2,INDEX(B1:B15,MATCH(MAX(B1:B15),B1:B15, 0)):B15,0))

Biff
"BillReese" wrote in message news:2N06g.14257$O_6.12998@trnddc08...
Thanks to Biff, I found 1/2 of what I need. I thought I could figure out how to plug in the Offset formula to his solution to give me what I want so far, but no luck. So I need another formula to do the following:

I have is a list like this:
A B
1 1.10

2 1.20

3 1.31

4 1.40

5 1.49

6 1.57

7 1.65

8 1.70

9 1.74

10 1.75

11 1.73

12 1.66

13 1.58

14 1.49

15 1.41

I want "2" VALUES

Value #1 ( Using the value in Column B located above "peak" value 1.74 )
I want value in Column A adjacent to the highest value < 1.6 (which is 1.57)
I want this returned ( #6 ) which is adjacent to 1.57

Value #2 ( Using the value in Column B located below "peak" value 1.74 )
I want value in Column A adjacent to the highest value < 1.6 (which is 1.58)
I want this returned ( #13 ) which is adjacent to 1.58

================================================== ============
These are the formulas Biff gave me to extract values:
For V1:
Entered as an array using the key combination of CTRL,SHIFT,ENTER:

=MAX(IF(B1:INDEX(B1:B15,MATCH(MAX(B1:B15),B1:B15,0 ))<C1,B1:INDEX(B1:B15,MATCH(MAX(B1:B15),B1:B15,0)) ))

For V2: (array entered)
=MAX(IF(INDEX(B1:B15,MATCH(MAX(B1:B15),B1:B15,0)): B15<C1,INDEX(B1:B15,MATCH(MAX(B1:B15),B1:B15,0)):B 15))

PS. I don't want the hash mark in the format.. I just want the numbers 6 and 13.
Thanks,
BillReese

AGAIN... I need another Lookup Function to extract some data
 

Hi Biff,

I just wanted to thank you, the answer you gave me was just what I needed to get a much more complicated code to work. You gave me all the clues I needed.

thanks very much, I don't believe I was capable without this help !!!

regards,

BR


"Biff" wrote in message ...
Assume:

F1 = V1 = 1.57
F2 = V2 = 1.58

For V1 (array entered):

=INDEX(A1:A15,MATCH(F1,B1:INDEX(B1:B15,MATCH(MAX(B 1:B15),B1:B15,0)),0))

For V2 (array entered):

=INDEX(A15:INDEX(A1:A15,MATCH(MAX(B1:B15),B1:B15,0 )),MATCH(F2,INDEX(B1:B15,MATCH(MAX(B1:B15),B1:B15, 0)):B15,0))

Biff
"BillReese" wrote in message news:2N06g.14257$O_6.12998@trnddc08...
Thanks to Biff, I found 1/2 of what I need. I thought I could figure out how to plug in the Offset formula to his solution to give me what I want so far, but no luck. So I need another formula to do the following:

I have is a list like this:
A B
1 1.10

2 1.20

3 1.31

4 1.40

5 1.49

6 1.57

7 1.65

8 1.70

9 1.74

10 1.75

11 1.73

12 1.66

13 1.58

14 1.49

15 1.41

I want "2" VALUES

Value #1 ( Using the value in Column B located above "peak" value 1.74 )
I want value in Column A adjacent to the highest value < 1.6 (which is 1.57)
I want this returned ( #6 ) which is adjacent to 1.57

Value #2 ( Using the value in Column B located below "peak" value 1.74 )
I want value in Column A adjacent to the highest value < 1.6 (which is 1.58)
I want this returned ( #13 ) which is adjacent to 1.58

================================================== ============
These are the formulas Biff gave me to extract values:
For V1:
Entered as an array using the key combination of CTRL,SHIFT,ENTER:

=MAX(IF(B1:INDEX(B1:B15,MATCH(MAX(B1:B15),B1:B15,0 ))<C1,B1:INDEX(B1:B15,MATCH(MAX(B1:B15),B1:B15,0)) ))

For V2: (array entered)
=MAX(IF(INDEX(B1:B15,MATCH(MAX(B1:B15),B1:B15,0)): B15<C1,INDEX(B1:B15,MATCH(MAX(B1:B15),B1:B15,0)):B 15))

PS. I don't want the hash mark in the format.. I just want the numbers 6 and 13.
Thanks,
BillReese