Pull info from First Column with Data

[reneev]

I have a spreadsheet with Positions down the side and Steps across the top and the rates in the columns. I need a formula that will pull the rate for the first available step into one column. So for receptionist, it would pull the Step 1 rate, for Technician it would pull the Step 3 rate and for Office Manager it would pull the Step 6 rate.

I need to be able to vlookup the first step rate for the position into another sheet. In the past, I have adjusted the vlookup formulas for each position, which is prone to errors.

If it can be done, you guys will know how to do it.

Thanks!

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Position[/TD]
[TD]First Step Rate[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[/TR]
[TR]
[TD]Receptionist[/TD]
[TD]21.50[/TD]
[TD]21.50[/TD]
[TD]23.50[/TD]
[TD]24.50[/TD]
[TD]25.00[/TD]
[TD]27.00[/TD]
[TD]29.00[/TD]
[TD]30.00[/TD]
[TD]35.00[/TD]
[TD]40.00[/TD]
[/TR]
[TR]
[TD]Technician[/TD]
[TD]24.50[/TD]
[TD][/TD]
[TD][/TD]
[TD]24.50[/TD]
[TD]25.00[/TD]
[TD]27.00[/TD]
[TD]29.00[/TD]
[TD]30.00[/TD]
[TD]35.00[/TD]
[TD]40.00[/TD]
[/TR]
[TR]
[TD]Office Manager[/TD]
[TD]29.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]29.00[/TD]
[TD]30.00[/TD]
[TD]35.00[/TD]
[TD]40.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[kweaver]

One way is to use this ARRAY formula: =INDEX(C2:K2,MATCH(FALSE,ISBLANK(C2:K2),0)) [enter with CTLR+Shift+Enter]

 

[DanteAmor]

Try a regular formula.

If the cells before the value are empty, you only need:
=MIN(C2:K2)

 

[kweaver]

Dante: that assumes all the values are always increasing.

 

[DanteAmor]

I simply take the example of the OP as a basis, if it is something different, then the OP will comment.

 

[reneev]

Try a regular formula.

If the cells before the value are empty, you only need:
=MIN(C2:K2)

This worked! Thank you. And yes, the rates would always be increasing.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.