Home |
Search |
Today's Posts |
#1
|
|||
|
|||
LOOKUP function
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 |
#2
|
|||
|
|||
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? -- Regards, Ron |
#3
|
|||
|
|||
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)) |
#4
|
|||
|
|||
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,D2:D151,0)) ,D2:D151))-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! In article , "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. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can i use > in lookup function? | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions | |||
Lookup Function Error | Excel Worksheet Functions | |||
Lookup Function Problem | Excel Discussion (Misc queries) | |||
Lookup function w/Text and Year | Excel Worksheet Functions |