Return value of last match

Question

I need a formula to return the value of Data for the last match of "Text". Row number is also acceptable. Macro is NOT acceptable. Name column is unsorted and cannot be sorted! Only column "Name" is used as lookup value. I would rather use a/multiple helper column(s) instead of an array formula.

Row Name Data
1   Joe  10
2   Tom  20
3   Eva  30
4   Adam 40
5   Tom  21

LARGE only works with numbers, and VLOOKUP only returns the first match. LOOKUP only works sometimes, so its out too.

So if I wanted the last match for "Tom" then it should return "21".

Answer 1

Create a column with an array formula (enter it with Ctrl+Shift+Enter):

=VLOOKUP(MAX(IF($B$2:$B$6=B2, $A$2:A$6, 0)), $A$2:$C$6, 3, FALSE)

To make sure you did it right, click on the cell, and the formula should be shown encased in curly brackets ({}).

Note: This assumes that "Row" is in A1.

Answer 2

I have come up with a solution, but it requires that numbers in Data are concurrent, like so

Name        Data
Joe         1
Tom         1
Eva         1
Adam        1
Tom         2
Tom         3
Eva         2

But thats okay, since that my data looks like that anyway. So if Name is used before then it must be the old highest +1 aka concurrent.

Name is A1 and Data is B1, and this formula goes into C2:

FLOOR(SQRT(2*SUMIF(A2:A7,A2,B2:B7)),1)

Answer 3

Array formulas could be avoided with a helper column. Suppose to have in F1 the name to match (i.e. Tom) In the helper column row C2 enter

=IF(A2<>$F$1,0,row())

Then copy the formulas along your data.

Now the column C contains 0 for the unmatched names and the row number for the matched ones. Maxing the column yield the row of the solution. Now the result is simple a matter of using the correct offset with the function offset:

=OFFSET(B1,max(C:C)-1,0)

PS: my copy of excel is in italian, so I can't test this english translaction of the formulas.