Formulas to find the first and last numeric value and text string in a column

Rambo4711,

Attached is an example workbook based on the criteria you described.
Column B has a mix of numbers, text, blanks, and cells with "" in them.
Column D contains the position number you want to return.

Column E gets numbers going from top to bottom with this array formula in cell E3 and copied down:

Please Login or Register  to view this content.

Column F gets text going from top to bottom with this array formula in cell F3 and copied down:

Please Login or Register  to view this content.

Column G gets numbers going from bottom to top with this array formula in cell G3 and copied down:

Please Login or Register  to view this content.

Column H gets text going from bottom to top with this array formula in cell H3 and copied down:

Please Login or Register  to view this content.

Note that when entering array formulas you have to confirm them with Ctrl+Shift+Enter and not just Enter. That is how the formula becomes surrounded with the curly braces {}. Do not try to add those yourself.

Table version

Thank's Tigeravatar and Jacc!

Works exactly as I wanted.

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.