View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
BillReese
 
Posts: n/a
Default 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