Formula to return position of the last non-blank cell

=LOOKUP(2;1/(Sheet1!E73:DV73<>"");Sheet1!E72:DV72) would do it
fyi how it works
=LOOKUP(2;1/(Sheet1!a2:e2<>"");Sheet1!a1:e1)
and in a2 to e2 you have
something blank something blank something
Sheet1!a2:e2<>"" gives an array of {1;0;1;0;1}
now when the reciprocal of the range is calculated
1/(Sheet1!a2:e2<>"")
returns an array of {1;#DIV/0!;1;#DIV/0!;1}
lookup() conveniently ignores error values (i dont know why its a quirk and just does!)
also if lookup cannot find a match in this case we are looking for 2
=LOOKUP(2;1/(Sheet1!a2:e2<>"");Sheet1!a1:e1)
ie lookup(2;{1;#DIV/0!;1;#DIV/0!;1};Sheet1!a1:e1)
then from the help files

If LOOKUP can't find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value.

what it doesn't say is if all are the same or less it takes the last one from the array
so in this case it finds the last 1 in the array
{1;#DIV/0!;1;#DIV/0!;1}
which happens to be position 5
it then returns the value in the corresponding position from the second array
Sheet1!a1:e1
ie the value in e1
hth

try:

Please Login or Register  to view this content.

One more approach..

Formula:

Please Login or Register  to view this content.