Return last matching value?

[djarcadian]

Please Login or Register  to view this content.

This formula searches from the top to find the matching value. Is there a way to have it search from the bottom of the range to find the last matching value?

[Pete_UK]

No, scrub that - I clicked on Submit Post before I tested it.

Pete

[FDibbins]

Im pretty sure that some1 will give you a better suggestion, but I would do this with a helper column using countif() to count the occurances, then use index/match/large, something like this...

	A	B	C	D
1	aa	1	1	7
2	bb	2	1	aa
3	cc	3	1
4	aa	4	2
5	bb	5	2
6	cc	6	2
7	aa	7	3
8	bb	8	3
9	cc	9	3

C1=COUNTIF($A$1:A1,A1)
D1=INDEX($B$1:$B$9,MATCH(D2&LARGE(IF($A$1:$A$9=A1,$C$1:$C$9,0),1),A1:A9&C1:C9,0))
This is an ARRAY formula and needs to be entered using CTRL SHT ENTER, not jusdt enter

d2=search criteria

[oeldere]

Maybe you could sort the data in Z - A (instead of A - Z).

After that you could use your own formula.