excelexcel-formula

Excel: Find last value in an array

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?

Solution

  • 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.