find last populated cell in range of cells

[Maximus]

I have a worksheet that contains the actual and projected hours for
workers and I'm trying to figure out the function that would search
through the entire row and return the last cell of the row that
contains a non-blank value. Some of the cells contain hours (ex: 1.0,
15.0, 3.5) and other cells are blank. Some of the cells are
completely blank in between the specific range. For example the first
column contains the workers names and after that are the hours, the
very first row in the sheet is the week that the hours are projected
for... for this example, semi colons are used to represent a new cell:
A ; B ; C ; D ; E ;
F ; G ; H ; I ; J
1; Name ; 7-Aug; 14-Aug; 21-Aug; 28-Aug; 4-Sep; 11-Sep; 18-
Sep; 25-Sep; Total
2; John Smith; 1.0 ; 2.0 ; 2.5 ; ; ;
5.5 ; 5.2 ; ; 16.2
3; Jane Doe ; 2.0 ; 5.0 ;
1.5 ; ; ; ; ; ;
8.5

I want the function to search through the range and return the date
that is above the last unblank cell.
Range -> B2:I2 returns 18-Sep (I don't want it to return 21Aug
because it is followed by a blank)
Range -> B3:I3 returns 21-Aug

This function needs to be entirely data driven and the only thing that
can really be hard coded is the row that contains the date that needs
to be returned.

Thanks for all the help!

 

[Rick Rothstein]

This should work for you...

=INDEX(A$1:I$1,1+MATCH(99999,B2:I2,1))

 

[Rick Rothstein]

Perhaps this version would be better in case nothing it filled in for the
employee (it shows an empty cell rather than an error))...

=IF(COUNT(B2:I2),INDEX(A$1:I$1,1+MATCH(99999,B2:I2,1)),"")

 

[Luke M]

Your example got a little skewed in the posting, but I believe this is what
you want.

=INDEX($1:$1,1,MAX(IF(NOT(ISBLANK(2:2)),COLUMN(2:2))))

Note that this is an array function, and needs to be confirmed using
Ctrl+Shift+Enter (hold down Ctrl+Shift, press Enter), not just Enter.

Formula is set to return value from row 1 that is above last nonblank cell
in row 2.

 

[Rick Rothstein]

(Haven't had my coffee yet this morning<g>)

Let's make that 2 characters shorter...

=IF(COUNT(B2:I2),INDEX(B$1:I$1,MATCH(99999,B2:I2,1)),"")

 

[Shane Devenshire]

Hi,

You could use

=INDEX(B$1:I$1,MATCH(9^9,B2:I2))

 

[T. Valko]

Here's another one:

=IF(COUNT(B2:I2),LOOKUP(1E100,B2:I2,B1:I1),"")

Format as Date