vlookup/match to return non blank value

[Guinea_72]

Hello,

The table below is a mock of a crane capacity sheet I am trying to manipulate. I have a spreadsheet that determines the capacity given an entered radius (leftmost column) and boom length (uppermost row) by using a vlookup/match formula and that works great. The problem is I want the formula to return a non blank value by searching down the matched list until it comes to a value. For example, the user would enter in a boom length of 90 and radius of 18. Right now the formula would return "N/A" when I want it to return 25000 and adjust the radius to 22 and inform the user of this adjustment. Any help would be greatly appreciated, thanks!

			Boom
Radius	70	80	90	100	120
16	225000
18	215000	200000
20	210000	185000
22	205000	165000	25000	25000	25000

<tbody>
</tbody>

 

[Aladin Akyurek]

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​		​	​	Boom​
2​	Radius​	70​	80​	90​	100​	120​		90​	18​	25000​
3​	16​	225000​	​	​	​	​
4​	18​	215000​	200000​	​	​	​
5​	20​	210000​	185000​	​	​	​
6​	22​	205000​	165000​	25000​	25000​	25000​
7​

In J2 control+shift+enter, not just enter:

=MIN(IF(INDEX($B$3:$F$6,0,MATCH(H2,$B$2:$F$2,0))>0,IF($A$3:$A$6>=I2,INDEX($B$3:$F$6,0,MATCH(H2,$B$2:$F$2,0)))))

 

[Guinea_72]

Now is there a way to return the radius length of 22 after the capacity is calculated? Would I turn around and do another index/match just using the returned capacity value?

 

[Aladin Akyurek]

Now is there a way to return the radius length of 22 after the capacity is calculated? Would I turn around and do another index/match just using the returned capacity value?

In K2 enter:

=INDEX($A$3:$A$6,MATCH(J2,INDEX($B$3:$F$6,0,MATCH(H2,$B$2:$F$2,0)),0))

 

[Guinea_72]

Works like a charm! Appreciate the help!

 

[Aladin Akyurek]

Works like a charm! Appreciate the help!

You are welcome.

 

[Guinea_72]

		Boom
Radius	70	80	90	100
16	25000
17	24000
18	23000	20000
20	21000	19000	10000
22	20000	18000	9000	8000
24	15000

<tbody>
</tbody>

Now I'm getting picky but say now the user enters in a boom of 90 and a radius 24, how do I make it adjust to the value of 9000 but also at the same time if the user would then enter in a boom of 80 and radius of 16 for it to return a value of 20000. Can I write this all in the same formula? Or what are my options? thanks!

 

[Aladin Akyurek]

		Boom
Radius	70	80	90	100
16	25000
17	24000
18	23000	20000
20	21000	19000	10000
22	20000	18000	9000	8000
24	15000

<tbody>
</tbody>

Now I'm getting picky but say now the user enters in a boom of 90 and a radius 24, how do I make it adjust to the value of 9000 but also at the same time if the user would then enter in a boom of 80 and radius of 16 for it to return a value of 20000. Can I write this all in the same formula? Or what are my options? thanks!

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​	​	24​	22​	22​	22​
2​	Radius\Boom	70	80	90	100	​		90​	24​	9000​
3​	16	25000				​		80​	16​	20000​
4​	17	24000				​
5​	18	23000	20000			​
6​	20	21000	19000	10000		​
7​	22	20000	18000	9000	8000
8​	24	15000

In B1 enter and copy across:

=LOOKUP(9.99999999999999E+307,B$3:B$8,$A$3:$A$8)

In J2 control+shift+enter, not just enter, and copy down:

=INDEX($B$3:$E$8,MIN(IF(INDEX($B$3:$E$8,0,MATCH(H2,$B$2:$E$2,0))>0,IF($A$3:$A$8>=MIN(I2,LOOKUP(9.99999999999999E+307,$B$2:$E$2,$B$1:$E$1)),ROW($A$3:$A$8)-ROW($A$3)+1))),MATCH(H2,$B$2:$E$2,0))