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!

 

[mrshl9898]

Are the Codes always consecutive?

=INDEX(B2:B$9,COUNTIF(A2:A$9,A2))

 

[Eric W]

How about?

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

Cell Formulas
Range	Formula
C2:C9	C2	=LOOKUP(2,1/($A$2:$A$10=A2),$B$2:$B$10)
D2:D9	D2	=IF(COUNTIF($A$2:$A2,A2)=1,LOOKUP(2,1/($A$2:$A$10=A2),$B$2:$B$10),"")

The D2 formula is if you only want to show the result on the first matching line.

 

[Toadstool]

Hi BiggusDoggus,

This formula should do what you ask:

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

Cell Formulas
Range	Formula
C2:C9	C2	=IF(A2="","",INDEX($B$2:$B$15,AGGREGATE(14,6,ROW($A$2:$A$15)-ROW($A$1)/($A$2:$A$15=A2),1)))

 

[BiggusDoggus]

How about?

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

Cell Formulas
Range	Formula
C2:C9	C2	=LOOKUP(2,1/($A$2:$A$10=A2),$B$2:$B$10)
D2:D9	D2	=IF(COUNTIF($A$2:$A2,A2)=1,LOOKUP(2,1/($A$2:$A$10=A2),$B$2:$B$10),"")

The D2 formula is if you only want to show the result on the first matching line.

The D2 formula is perfect - thank you!

 

[Eric W]

Glad we could help!

 

[BiggusDoggus]

.

 

[BiggusDoggus]

Glad we could help!

Sorry to bump this - but what would the formula be to get the FIRST text value please?

 

[Eric W]

Do you mean this?

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

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

 

[BiggusDoggus]

Do you mean this?

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

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

That would be it, yes thank you! And to have the value appear on every matching line please?