# vlookup/match to return non blank value

#### Guinea_72

##### New Member
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>

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### Aladin Akyurek

##### MrExcel MVP
 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

##### New Member
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

##### MrExcel MVP
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

##### New Member
Works like a charm! Appreciate the help!

#### Guinea_72

##### New Member
 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

##### MrExcel MVP
 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))

Replies
1
Views
472
Replies
9
Views
6K
Replies
6
Views
730
Replies
2
Views
533
Replies
3
Views
2K

Threads
1,191,683
Messages
5,987,991
Members
440,124
Latest member
dippy_egg

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back