Return last non-zero value in Excel

I recently needed to find a formula that returned the last value in a row that wasn't zero. The problem is the value I wanted to return could be in different positions in each row. The answer is LOOKUP. You may be familiar with VLOOKUP or HLOOKUP but you can also use LOOKUP by itself. The screen show below shows 3 rows of data all with values in different positions in each row. I need to create a new column called Last Price and pickup the most recent price in the row.

The formula I need to use in column N (Last Price) is as follows;

The formula takes a bit of explaining.

1/(B2:M2<>0) returns a list of TRUE or FALSE, which when divided by 1 converts this list to either 1’s or 0’s. LOOKUP(2, is looking for the number 2 in the list but will never find it because the list only contains 1’s or 0’s. Therefore so the formula will find the last 1 in the list – the remaining part of the formula is the range where the actual values are, so the same lookup range (B2:M2). T aking the 2ND row as an example, the first part of the formula looked in the range B3:M3 and returned 1,1,1,1,1,1, 1 ,0,0,0,0,0. It looked for the value of 2 but couldn’t find it so found the next number down 1 (in bold). The actual values in that range are 7.5,7.5,7.5,7.5,7.5,7.5, 7.5 ,0,0,0,0,0 so the returned value is 7.5

Takes a bit of getting your head round but once you understand what it’s doing it makes sense.