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 might want to try this, although it forces you to make one extra array, it looks for the first occurrence: Put your fruit data in A1:A10 and add an extra column next to it in B1:B10 (this is important and alas mandatory (see VLOOKUP description : it has to be ?1:?10)) with numbers from 1 to 10

To populate column B you can use, depending on your needs, formulas like

= ROWS($B$1:B1)
= ROW() + offset

Then the formula that will get your information is VLOOKUP (HLookup if your data array is horizontal). It will look for the value in the leftmost column of the argument matrix and return the matching value in the 2nd column (3rd argument, column B in our case). The FALSE is to require an exact match.

= VLOOKUP("orange", A1:B10, 2, FALSE)

Remember the drawbacks: * You have to add one extra data column, be it convenient or not * It will look for the first result. period.

(I am still searching for a better way to really find the MIN and MAX of an array of findings, but no success yet, except with Ctrl-Shift-Enter formulas, which are a no-go. Please post back if you find it)

Answer 2

Let's suppose we have a horizontal array of fruit names in A1:J1. The column number for the last occurence of "apple" would be:

{=MAX(COLUMN(A1:J1)*(A1:J1="apple"))}

Don't forget to press Ctrl+Shift+Enter, it's an array formula.

It's the same idea as PPC's concept of a bit mask & sequential numbers, but invented independently and expressed in a much more compact way. :) I haven't given it a big stress test, but I saw no problem using more complicated formulas in multiple places on hundreds of items in each instance, which is quite enough for me.

Answer 3

Write a user defined function to search the data backward from the last cell

Function LastFruit(r As Range, Fruit As String) As Range
    Dim rw As Long, col As Long
    For rw = r.Rows.Count To 1 Step -1
        For col = r.Cells.Count To 1 Step -1
            If r.Cells(rw, col) = Fruit Then
                Set LastFruit = r.Cells(rw, col)
            End If
        Next
    Next

End Function

Answer 4

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.

Answer 5

Another very different solution, which very few people will like: the goal is to use a huge number that contains all matches as a bitmask. Then, using arithmetics, you can find the last match.

Disclaimer: this solution is

• Inelegant
• computationnaly heavy
• will overflow with moderately big records (for me ~1015). Overflow can cause clean errors (#NUM) and might also give a wrong number due to excessive float rounding (I have not observed it but it's still possible)

You need to have an array of sequential numbers of the same size as your dataset (doesn't have to be close though). If your fruits are in A1:A10, you can put the values (1..10) in Z1:Z10.

= FLOOR(IMLOG2( 
    SUMPRODUCT( (A1:A10 = "orange")*1 ; Z1:Z10
  ) ; 1)

Let's look at it:

• SUMPRODUCT will make the bitmask containing 1s wherever you have the orange word
• IMLOG2 (Why doesn't Excel have a Real numbers log2?) will get you the (float) log2 of the mask
• FLOOR will truncate it, the result is the "biggest index" of 1s in the bitmask

Hopefully you will find other arithmetical operations for finding other matches