Finding first (and last) entry in a long row

[lubbockdutchman]

I am looking for a simple formula to find the position of the first non-blank entry in a long row of cells. I am also looking for another simple formula to find the position of the last non-blank entry in the same long rom of numbers. The enclosed spreadsheet has three lines of examples with the desired answers in the yellow highlighted cells.

I can used a long series of nested IF statements but there must be a easier solution. None of the built-in functions seem to provide a solution. I have also looked through many previous posts without finding a solution.

Thanks for any help.

[FDibbins]

Hi, welcome to the forum

Try this for the 1st...
=MATCH(1,D3:Z3,-1)

And this for the last...
=MATCH(1,D3:Z3,1)

[lubbockdutchman]

Thank you so much. It worked perfectly with the supplied data, but does not work with other data. I am uploading a revised worksheet with more realistic data.

[lubbockdutchman]

Thank you so much FDibbins. It worked perfectly with the initial supplied data; however, it does not work with other data. I have uploaded a revised worksheet with data.

[Steebo]

Okay I'm a forum newbie but I took a stab at it.

To get the first (cell AB)

Please Login or Register  to view this content.

and the last (cell AC)

Please Login or Register  to view this content.

[lubbockdutchman]

Thanks Steebo! Your formulas work correctly when entered as array formulas {Control+Shift+Enter}.

An alternative to the MATCH formula to find the first entry would be:

Please Login or Register  to view this content.

Note that the -3 would need to be adjusted if the first column of interest was other than column "D".

I only wish that there were a simpler and more intuitive formula.

[Aladin Akyurek]

[...]

A3, copied down:

=MATCH(TRUE,INDEX(ISNUMBER(D3:Z3),0),0)

B3, copied down:

=MATCH(9.99999999999999E+307,INDEX(1/D3:Z3,0))

[lubbockdutchman]

Aladin your solution works very well! The functions are not array formulas which is also an advantage.

I do not understand one part of the formula - the "1/". It also works without it. I can not find any other sources which describe the use of the "1/". What is its purpose?

[Aladin Akyurek]

Aladin your solution works very well! The functions are not array formulas which is also an advantage.

I do not understand one part of the formula - the "1/". It also works without it. I can not find any other sources which describe the use of the "1/". What is its purpose?

That would allow you to exclude 0 entries. If that's not intended, we have to drop the division:

=MATCH(9.99999999999999E+307,INDEX(D3:Z3,0))