Finding the last text value based on cell value in another column

[BiggusDoggus]

Hi all

So I have a table of data - here is an example:

	A	B	C
1	Code	Stage	Final Stage
2	455	Stage 1
3	455	Stage 2
4	522	Stage 1
5	522	Stage 1
6	616	Stage 1
7	616	Stage 2
8	616	Stage 3
9	616	Stage 4

What I'm wanting in column 3 is a formula to return the stage in column B relating to the last occurrence of the code in column A.

So - for code 455 it would be Stage 2, code 522 Stage 1, code 616 Stage 4.

I can't use MAX obviously because that doesn't work for text values, but I have been unable to locate an alternative.

Any help appreciated please!

 

[Eric W]

Try:

Book1
	A	B	C	D
1	Code	Stage	First Stage	Final Stage
2	455	Stage 1	Stage 1	Stage 2
3	455	Stage 2	Stage 1	Stage 2
4	522	Stage 1	Stage 1	Stage 1
5	522	Stage 1	Stage 1	Stage 1
6	616	Stage 1	Stage 1	Stage 4
7	616	Stage 2	Stage 1	Stage 4
8	616	Stage 3	Stage 1	Stage 4
9	616	Stage 4	Stage 1	Stage 4
Sheet7

Cell Formulas
Range	Formula
C2:C9	C2	=VLOOKUP(A2,A$2:B2,2,0)
D2:D9	D2	=LOOKUP(2,1/($A$2:$A$10=A2),$B$2:$B$10)

 

[BiggusDoggus]

Try:

Book1
	A	B	C	D
1	Code	Stage	First Stage	Final Stage
2	455	Stage 1	Stage 1	Stage 2
3	455	Stage 2	Stage 1	Stage 2
4	522	Stage 1	Stage 1	Stage 1
5	522	Stage 1	Stage 1	Stage 1
6	616	Stage 1	Stage 1	Stage 4
7	616	Stage 2	Stage 1	Stage 4
8	616	Stage 3	Stage 1	Stage 4
9	616	Stage 4	Stage 1	Stage 4
Sheet7

Cell Formulas
Range	Formula
C2:C9	C2	=VLOOKUP(A2,A$2:B2,2,0)
D2:D9	D2	=LOOKUP(2,1/($A$2:$A$10=A2),$B$2:$B$10)

Awesome - thank you