Search Array and return column/row data

Copper Contributor

 

 

 

 

 

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

@JMBalik 

Could you please explain bit more your example. Greater or equal to 26 is 26.2. It gives 80 and 110. Please see attached.

 

@Sergei Baklan 

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

@Sergei Baklan 

 

The example you sent is essentially what I'm trying to accomplish.

 

Thanks for your time.

@Metrojmb 

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:

image.png

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