Return the last entered value in a column or array

Question

In order to use an integration between another platform and Excel, I need to have the integration always look at a single cell which will display the last value entered.

If all of the values are entered, I found that I can use the formula '=INDEX(B2:B12,COUNTA(B2:B12),1) to return the last value as seen in B15.  This is basically an index/match, but the match is replaced with counta to get the row number.  As you can see, the "Widgets Sold" column works fine.  This formula stops working though if values are missing.  For Gizmos Sold in column C, I would like the last value entered, in this case 50.  What formula could I put in C15 in order to make it display the last value entered from C2:C13?

Answer 1

Hello Josh

I am V. Arya, Independent Advisor, to work with you on this issue. You can use following formula

=1/LOOKUP(1,1/B2:B13)

Answer 2

Last Numeric Value in Column (formula in cell B15):

=LOOKUP(9.99999999999999E+307,B2:B13)

Alternatively, you may also use instead of above:

=LOOKUP(1E+100,B2:B13)

Last Value in column, numeric or text - formula in cell B16:

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

Note: The above will work for column C also.

Regards,

Amit Tandon

www.excelanytime.com