Excel: Find last value in an array

Question

I have a dataset with

> A b c d...AA,BB
>1,2,3,4 
> apple apple apple
> orange pear pear apple pear
> grapefruit,grape, grape,grape

Is there a way to find the final occurence of a particular fruit in the array automatically via formula in Excel?

Answer 1

You need to use counta to tell you how many items are in the array and index to get the value of the last element.

You can try

=INDEX(1:1,0,COUNTA(1:1))

This will find the last value in the 1:1 array.