00:00I recently shared a video demonstrating how to retrieve the last value in a row.
00:04However, some viewers pointed out that the method will not function properly
00:08in cases where there are gaps within your data set like this.
00:12For those encountering such data patterns,
00:15here's how you can still get the last value in a row.
00:18We're going to use a lookup function looking for value 2
00:22in an array generated by 1 divided by the logical expression
00:26C5 to I5 not equal to blank.
00:30Just keep in mind that when you divide 1 by true, it returns 1.
00:34But when you divide 1 by false, you're going to get an error.
00:39And as a third argument, which is our result vector,
00:41it would be, yes, you guessed it, C5 to I5.
00:45Now, the formula might look a bit odd at the first glance
00:49because we're using 2 as a lookup value.
00:52However, the purpose of using 2 in the formula is actually
00:55a clever trick to exploit the behavior of a lookup function in Excel.
00:59In this formula, the lookup value of 2 isn't actually important
01:04for the lookup process itself.
01:05Instead, it serves as a way to guarantee that lookup will always return
01:09the last non-empty cell in the range C5 to I5.
01:14in this formula.
01:14In this formula, the lookup value of 3 is now.
01:16In this formula, the idea of the method,
Comments