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

B

BillReese

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)):B15))

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

Biff

Assume:

F1 = V1 = 1.57
F2 = V2 = 1.58

For V1 (array entered):

=INDEX(A1:A15,MATCH(F1,B1:INDEX(B1:B15,MATCH(MAX(B1: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
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)):B15))

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

BillReese

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


Assume:

F1 = V1 = 1.57
F2 = V2 = 1.58

For V1 (array entered):

=INDEX(A1:A15,MATCH(F1,B1:INDEX(B1:B15,MATCH(MAX(B1: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
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)):B15))

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Lookup function experts.. I need ascending and decending lookups of a series of data.. can you h 6
Is there any way to use HLOOKUP in an array formula to return multiple matches? 9
need a search/find/lookup function 4
How do I tell Excel to insert data from a cell 4 to the left? 1
Adding cells until they are greater than a lookup cell and returning a value 1
Separating equal values 2
Wildcard issue for Index/Match Function when searching numbers 1
Horizontal and Vertical lookup 2

Top