find column number from lookup

[jillrein]

Hi

I am trying to return the date in row 1 from the last used cell in a row. I can find the value of the last used cell using =LOOKUP(2,1/(A2:E2<>""),A2:E2). I tried to use the column function with this formula to return the column number but it doesn't work. I was going to use the column number with offset to return the date in row 1. Can anyone tell me how I can get the date in the last date column? I need to use this on a network that won't allow any VBA code.

 

[Leo Skywalker]

Hi there,

Try this:

I2:

=INDEX($A$1:$F$1,MATCH(LOOKUP(2,1/(A2:F2<>""),A2:F2),A2:F2,0))

 

[Leo Skywalker]

Hi there,

Try this:

I2:

=INDEX($A$1:$F$1,MATCH(LOOKUP(2,1/(A2:F2<>""),A2:F2),A2:F2,0))

or better yet, simply:

=LOOKUP(2,1/(A2:F2<>""),$A$1:$F$1)

 

[jillrein]

Hi there,

Try this:

I2:

=INDEX($A$1:$F$1,MATCH(LOOKUP(2,1/(A2:F2<>""),A2:F2),A2:F2,0))

Thank you Leo. That is exactly what I need.