Forum Discussion
Search Array and return column/row data
| I want to search the array for a value, have the array evaluate the number and return a value equal to or greater than the searched value and get the row and column information of the intersection point. See the array below. E.G. input 26, get 85 & 109 as the response. Thanks in advance for any help you can provide. | |||||
| ||||||
88 | 85 | 80 | 75 | 70 | 65 | |
68 | 36.7 |
|
|
|
|
|
70 | 36.1 |
|
|
|
|
|
80 | 33.1 |
|
|
|
|
|
84 | 31.8 | 34.1 |
|
|
|
|
90 | 29.8 | 32.1 |
|
|
|
|
95 | 27.9 | 30.6 |
|
|
|
|
100 | 25.9 | 29.0 |
|
|
|
|
109 | 22.3 | 26.5 | 29.5 |
|
|
|
110 | 21.9 | 26.2 | 29.3 |
|
|
|
115 | 20.3 | 24.6 | 27.9 |
|
|
|
120 | 18.6 | 23.0 | 26.5 |
|
|
|
125 | 17.3 | 21.3 | 25.2 |
|
|
|
130 | 16.0 | 19.5 | 23.9 |
|
|
|
134 | 15.1 | 18.3 | 22.9 | 25.5 |
|
|
140 | 13.8 | 16.6 | 21.5 | 24.0 |
|
|
145 | 13.0 | 15.4 | 20.3 | 22.9 |
|
|
150 | 12.1 | 14.2 | 19.0 | 21.7 |
|
|
158 | 11.0 | 12.7 | 16.4 | 19.9 | 20.3 |
|
160 | 10.7 | 12.3 | 15.7 | 19.5 | 20.0 |
|
165 | 10.2 | 11.5 | 14.4 | 18.5 | 19.2 |
|
170 | 9.7 | 10.7 | 13.1 | 17.4 | 18.3 |
|
175 | 9.1 | 10.0 | 12.1 | 15.8 | 17.6 |
|
180 | 8.4 | 9.3 | 11.0 | 14.2 | 16.8 |
|
181 | 8.3 | 9.2 | 10.8 | 13.9 | 16.7 | 15.4 |
190 | 7.3 | 8.3 | 9.3 | 11.6 | 15.5 | 14.2 |
195 | 6.7 | 8.0 | 8.7 | 10.6 | 14.1 | 13.7 |
200 |
| 7.6 | 8.0 | 9.6 | 12.6 | 13.1 |
202 |
| 6.5 | 7.8 | 9.3 | 12.1 | 12.9 |
210 |
|
| 7.1 | 8.0 | 10.1 | 12.0 |
213 |
|
| 6.2 | 7.6 | 9.5 | 11.7 |
220 |
|
|
| 6.7 | 8.1 | 10.9 |
224 |
|
|
| 6.0 | 7.5 | 9.9 |
230 |
|
|
|
| 6.6 | 8.5 |
235 |
|
|
|
| 5.8 | 7.5 |
240 |
|
|
|
|
| 6.5 |
245 |
|
|
|
|
| 5.5 |
|
|
|
|
|
|
|
5 Replies
Could you please explain bit more your example. Greater or equal to 26 is 26.2. It gives 80 and 110. Please see attached.
The example you sent is essentially what I'm trying to accomplish.
Thanks for your time.
As variant that could be
=INDEX(rowData, INDEX( SUMPRODUCT((data=MINIFS(data,data,">=" & M6))*ROW(data))-ROW(data)+1, 1))for the value in left row and
=INDEX(columnData, INDEX( SUMPRODUCT((data=MINIFS(data,data,">=" & M6))*COLUMN(data))-COLUMN(data)+1, 1))for one in columns:
This is a crane capacity chart. In many occasions the weight if a lifted load is known. The "idea" would require inputting the weight then searching multiple capacity charts to assist in selecting a crane.
Thanks
