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 o...
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:
- Thank you, I'll try to decipher your formulas.
I assume rowData refers to the 88, 85, 80, etc. row on the example spread sheet.
columnData refers to the 68, 70, 80, 84 column etc.
The initial "data" in both formulas refers to the actual array as does the second "data"
The third "data" would be a singe cell containing the search term.
If I send you the actual spreadsheet could you help clarify my questions ?
Again, thank you for all your time & help
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
