INDEX - Finding the NEXT non-blank column

Hello Excel Experts,

Just wondering if there's a formula to find the NEXT non-empty column.

i.e.
col A - has value
col B - has value
col C - blank
col D - blank
col E - has value.
col F - blank
col G - has value

Using the above example, given an INDEX numerical number (3),

Please Login or Register  to view this content.

makes it to column C where it would give a 0 (zero) value which is a blank. Is there a way to go to the NEXT NON-BLANK column, which the answer in this example would be 'E'. Number 3 is a value in a cell (say A2) and ColumnNameRange has the capability/intelligence in knowing the the beginning and the end of the column (in this example, col A to col G)

Thanks for replying Dilipandey! I've tried to modified a little and that is instead of having the numerical 3 manually input on the INDEX function, I want it to be variable, since users will be entering an integer numbers. Thus in your attached spreadsheet, I make it to reference to a cell (say B2).

I've also checked to see that the formula that you've created in D5 or D6 does not change whether through referencing to a cell (B2) or through manually change the numeric value in the INDEX function.

Also Dilipandey, to be a little more specific (sorry for not saying it earlier in the first post), After I've got the column index of the NEXT available non empty column, I want to

i.e.
col A - has value
col B - has value
col C - blank
col D - blank
col E - has value.
col F - blank
col G - has value

With the formula, assuming a numeric 3 (again) that's on a cell (say A2), I want it to be "smart" enough to have a range cell of B1 to D1 where as you've noticed, column B is actually the 'last' cell that has values before column C and column D is the 'last' cell that was blank value, before column E which has a value. This way, I could specifically look values in this specified range.

Solve through modifying similar function at MrExcel Forum.