LOOKUP function

[Heath]

I am trying to pull numbers out of the same row of different columns but I am
not getting the correct value. This is because I am unable to sort the data
in the lookup vector. I have to keep the data sorted in order by the first
column. Specifically I am trying to retreive the Max value from the third
column and get the corresponding numbers in the other two columns. Is there
a way to do this without sorting my lookup vector? included is my sheet: I
would like the value returned to be .96 because that number coresponds to my
Max value in column F but it instead returns .92 which is the last number in
column E. the function in B8 is: =LOOKUP(C4,F3:F152,E3:E152) where
C4=MAX(F3:F152). Thank you for any help you may be able to give.

B C D E F
Amount Over Price Dollars
3 1 1.069 909.719
4 Dollars Max 921.6 2 1.068 909.936
5 Corresponding Price ???? 3 1.067 910.151
6 Crspnding Amnt ???? 4 1.066 910.364
7 5 1.065 910.575
8 0.92 6 1.064 910.784
9 7 1.063 910.991
10 8 1.062 911.196
11 9 1.061 911.399
12 10 1.06 911.6
13 11 1.059 911.799
14 12 1.058 911.996
15 13 1.057 912.191
16 14 1.056 912.384
17 15 1.055 912.575
18 16 1.054 912.764
19 17 1.053 912.951
20 18 1.052 913.136
21 19 1.051 913.319
22 20 1.05 913.5
23 21 1.049 913.679
24 22 1.048 913.856
25 23 1.047 914.031
26 24 1.046 914.204
27 25 1.045 914.375
28 26 1.044 914.544
29 27 1.043 914.711
30 28 1.042 914.876
31 29 1.041 915.039
32 30 1.04 915.2
33 31 1.039 915.359
34 32 1.038 915.516
35 33 1.037 915.671
36 34 1.036 915.824
37 35 1.035 915.975
38 36 1.034 916.124
39 37 1.033 916.271
40 38 1.032 916.416
41 39 1.031 916.559
42 40 1.03 916.7
43 41 1.029 916.839
44 42 1.028 916.976
45 43 1.027 917.111
46 44 1.026 917.244
47 45 1.025 917.375
48 46 1.024 917.504
49 47 1.023 917.631
50 48 1.022 917.756
51 49 1.021 917.879
52 50 1.02 918
53 51 1.019 918.119
54 52 1.018 918.236
55 53 1.017 918.351
56 54 1.016 918.464
57 55 1.015 918.575
58 56 1.014 918.684
59 57 1.013 918.791
60 58 1.012 918.896
61 59 1.011 918.999
62 60 1.01 919.1
63 61 1.009 919.199
64 62 1.008 919.296
65 63 1.007 919.391
66 64 1.006 919.484
67 65 1.005 919.575
68 66 1.004 919.664
69 67 1.003 919.751
70 68 1.002 919.836
71 69 1.001 919.919
72 70 1 920
73 71 0.999 920.079
74 72 0.998 920.156
75 73 0.997 920.231
76 74 0.996 920.304
77 75 0.995 920.375
78 76 0.994 920.444
79 77 0.993 920.511
80 78 0.992 920.576
81 79 0.991 920.639
82 80 0.99 920.7
83 81 0.989 920.759
84 82 0.988 920.816
85 83 0.987 920.871
86 84 0.986 920.924
87 85 0.985 920.975
88 86 0.984 921.024
89 87 0.983 921.071
90 88 0.982 921.116
91 89 0.981 921.159
92 90 0.98 921.2
93 91 0.979 921.239
94 92 0.978 921.276
95 93 0.977 921.311
96 94 0.976 921.344
97 95 0.975 921.375
98 96 0.974 921.404
99 97 0.973 921.431
100 98 0.972 921.456
101 99 0.971 921.479
102 100 0.97 921.5
103 101 0.969 921.519
104 102 0.968 921.536
105 103 0.967 921.551
106 104 0.966 921.564
107 105 0.965 921.575
108 106 0.964 921.584
109 107 0.963 921.591
110 108 0.962 921.596
111 109 0.961 921.599
112 110 0.96 921.600
113 111 0.959 921.599
114 112 0.958 921.596
115 113 0.957 921.591
116 114 0.956 921.584
117 115 0.955 921.575
118 116 0.954 921.564
119 117 0.953 921.551
120 118 0.952 921.536
121 119 0.951 921.519
122 120 0.95 921.5
123 121 0.949 921.479
124 122 0.948 921.456
125 123 0.947 921.431
126 124 0.946 921.404
127 125 0.945 921.375
128 126 0.944 921.344
129 127 0.943 921.311
130 128 0.942 921.276
131 129 0.941 921.239
132 130 0.94 921.2
133 131 0.939 921.159
134 132 0.938 921.116
135 133 0.937 921.071
136 134 0.936 921.024
137 135 0.935 920.975
138 136 0.934 920.924
139 137 0.933 920.871
140 138 0.932 920.816
141 139 0.931 920.759
142 140 0.93 920.7
143 141 0.929 920.639
144 142 0.928 920.576
145 143 0.927 920.511
146 144 0.926 920.444
147 145 0.925 920.375
148 146 0.924 920.304
149 147 0.923 920.231
150 148 0.922 920.156
151 149 0.921 920.079
152 150 0.92 920

 

[Ron Coderre]

Is this what you're looking for?:
=SUMPRODUCT((F3:F152=MAX(F3:F152))*E3:E152)

It returns the Price from the same row where the maximum dollars are found.
(Note: this only works where the max value ony occurs once.)

Does this help?

 

[Harlan Grove]

Heath wrote...

I am trying to pull numbers out of the same row of different columns but I am
not getting the correct value. This is because I am unable to sort the data
in the lookup vector. I have to keep the data sorted in order by the first
column. Specifically I am trying to retreive the Max value from the third
column and get the corresponding numbers in the other two columns. Is there
a way to do this without sorting my lookup vector? included is my sheet: I
would like the value returned to be .96 because that number coresponds to my
Max value in column F but it instead returns .92 which is the last number in
column E. the function in B8 is: =LOOKUP(C4,F3:F152,E3:E152) where
C4=MAX(F3:F152). Thank you for any help you may be able to give.

....

You have to use INDEX and MATCH for this.

=INDEX(E3:E152,MATCH(C4,F3:F152,0))

 

[Domenic]

Here's an approach that will return the record with the maximum
'Dollars' amount and, in addition, will also return all other records
equalling the maximum amount, if others exist...

Assumptions:

1) Columns A, B, and C contain your data

2) The first row contains your headers/labels

Formulas:

D2, copied down:

=RANK(C2,$C$2:$C$151)+COUNTIF($C$2:C2,C2)-1

E1: enter 1 (indicating a Top 1 list)

F1:

=MAX(IF(C2:C151=INDEX(C2:C151,MATCH(E1,D2151,0)),D2151))-E1

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

G2, copied across and down:

=IF(ROW()-ROW(G$2)+1<=$E$1+$F$1,INDEX(A:A,MATCH(ROW()-ROW(G$2)+1,$D:$D,0)
),"")

This approach gives you some versatility. For example, if you're
interested in listing the Top 10, enter 10 in E1.

Hope this helps!