Ytdyklly

Tried a couple of formulae to try and get the most recent value in a row as per the below but without success - only seems to work when the data has no blanks??

On the basis of the conversation between us through comments, I would like to suggest you a method will first find the Last Entry and then after finds the corresponding Column Header.

1. 
   

   Write this Formula to get the Last Entered Value in Row, in Cell F3 & fill it Down.

   
   
   

   =LOOKUP(2,1/(A3:E3<>""),A3:E3)
   
   

   
   


2. 
   

   To get the corresponding Column Header enter this Array Formula in Cell G3,
   
   finish it with Ctrl+Shift+Enter & fill Down.

   
   
   

   {=INDEX($A$1:$E$1,MAX(IF($A$3:$E$5=F3,COLUMN($A$3:$E$5)-COLUMN(A1)+1)))}
   
   

   
   

N.B.

• I've used two different Formulas Because basically you want to get the Lats Entered Value and getting the Header Name is an optional.


• Adjust cell references in the Formula as you needed.

I have been looking at this for some time and I have not been able to reproduce the answers when I use different data, the problem being that LOOKUP() requires the look-up vector to be sorted, which is not the questioner's case.

Using reduced data (to save typing) I used 5 rows of 5 columns:-

For clarity I have built up the formulae over 3 columns:-

[G3]  =SUMPRODUCT(NOT(ISBLANK(A3:E3))*2^COLUMN(A3:E3))

[H3]  =INT(LOG(G3,2))

[I3]  =OFFSET(A3,0,H3-1)

Then G3:H3 are copied down the number of rows required.

Notes:-

• 
  G3 is a bit map of which cells have data.


• 
  H3 is the highest power of 2 in the bit map.


• 
  I3 is the contents of the last cell with data.


• The formulae can be nested, eg the last column number could be =INT(LOG(SUMPRODUCT(NOT(ISBLANK(A3:E3))*2^COLUMN(A3:E3)),2))
  


• I developed this with LibreOffice, and tested it with Excel 2007.

Since the values in the range are numeric, you can use simply:

=LOOKUP(9.9E+307,$E11:$P11)

Since lookup_value will be higher than any value in the row, the formula will return the last numeric value in that row.

If you want to return the month name, then put that row in for the result_vector argument.

=LOOKUP(9.9E+307,$E11:$P11,$E$10:$P$10)

If the row is longer than E:P, adjust the parameter accordingly.

If the last column you want might contain text, then try:

=LOOKUP(2, 1/LEN($E11:$P11),$E11:$P11)

I think HELP for the LOOKUP function is incorrect. See my correction:

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