Excel formula to find last column with data

If you're entering data on an Excel spreadsheet in successive columns, sometimes using all of the columns to the right, but often only using some of these columns, you can use a formula to find which is the last column with an entry.

Use the LOOKUP formula to find the first cell in a range on a particular row that does not have data entered. It's necessary to enter the both the first column and the last possible column that may contain data - in this example column E.

=LOOKUP(2,1/ (B7:E7<>""),ROW(A:A))

So the formula lookups up 2 and then finds where in the range from columns B to E the value nearest appears and then returns the number of the row in which that value appears. So if in this example in row 7 we look for the first column in which there is no entry, which in this case is the third in the array - 1/3 equals .333, the closet value to 2 (the blank entry in the fourth column would equal 1/4 .25 - not as close to 2 as .333) and then we return the result - the number of the column where this value appears.