CODEWITHSUNDEEP
excelexcel-formula
Kim
Kim

Reputation: 2787

Return value of last match

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

Upvotes: 0

Views: 7439

Answers (4)

Makah
Makah

Reputation: 4523

I think it's the easiest way to make it.

=LOOKUP("Tom";A2:B7)

Upvotes: 1

momobo
momobo

Reputation: 1775

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.

Upvotes: 1

Kim
Kim

Reputation: 2787

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)

Upvotes: 0

Eric
Eric

Reputation: 95243

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.

Upvotes: 0

Related Questions