Last Value

=INDEX(B2:F2,1,MAX(IF(B2:F2<>"",COLUMN(B2:F2)))-COLUMN(B2)+1)

I have used the above formula - it is exactly what i want but i am getting a #VALUE result. What have i done wrong?

Moved to correct forum, added link to thread being referenced.

http://www.excelforum.com/tips-and-t...-in-a-row.html

Originally Posted by ExcelTip

To find the last value (of any type) in each row, use the INDEX, MAX, IF, and COLUMN functions as shown in the following Array formula in column I:
{=INDEX(B2:F2,1,MAX(IF(B2:F2

I think part of that second formula is missing. But an array formula is not required to find the last value in a range, a normal lookup will do it:

=LOOKUP(2, 1/(B2:F2<>""), B2:F2)

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

Please do not hijack old threads - I have moved all posts to a new thread.