selecting last values in a list

[Jon von der Heyden]

Hi,

I have a list that needs to be sorting in ascending order. It's made up of three columns {Period;Code;Rate}

I need to return the latest rate for each code i.e. from the last period. I initially used a pivot table but then I discovered that the last period doesn't necessarily include all codes, so I would need to refer to the period prior to that if omitted.

Any suggestions?

Book1
	B	C	D	E	F	G
2	Period	Code	Rate		Sum of Rate	Period
3	Aug-05	1	-16		Code	Oct-05
4	Aug-05	2	-17		1	-24
5	Aug-05	4	-18		2	-25
6	Sep-05	1	-19		4	-26
7	Sep-05	2	-20		5	-27
8	Sep-05	3	-21
9	Sep-05	4	-22
10	Sep-05	5	-23
11	Oct-05	1	-24
12	Oct-05	2	-25
13	Oct-05	4	-26
14	Oct-05	5	-27
Sheet1

 

[Aladin Akyurek]

Hi,

I have a list that needs to be sorting in ascending order. It's made up of three columns {Period;Code;Rate}

I need to return the latest rate for each code i.e. from the last period. I initially used a pivot table but then I discovered that the last period doesn't necessarily include all codes, so I would need to refer to the period prior to that if omitted.

Any suggestions?
...

Book1
	A	B	C	D	E	F	G	H	I
1
2	Period	Code	Rate			Code	Last Rate	Period
3	Aug-05	1	-16			1	-24	Oct-05
4	Aug-05	2	-17			2	-25	Oct-05
5	Aug-05	4	-18			3	-21	Sep-05
6	Sep-05	1	-19			4	-26	Oct-05
7	Sep-05	2	-20			5	-27	Oct-05
8	Sep-05	3	-21
9	Sep-05	4	-22			Code	Last Rate	Period	Pos
10	Sep-05	5	-23			1	-24	Oct-05	9
11	Oct-05	1	-24			2	-25	Oct-05	10
12	Oct-05	2	-25			3	-21	Sep-05	6
13	Oct-05	4	-26			4	-26	Oct-05	11
14	Oct-05	5	-27			5	-27	Oct-05	12
15
Sheet1

G3, copied down:

=LOOKUP(2,1/($B$3:$B$14=F3),$C$3:$C$14)

H3, copied down:

=LOOKUP(2,1/($B$3:$B$14=F3),$A$3:$A$14)

More efficient:

G10, copied down:

=INDEX($C$3:$C$14,I10)

H10, copied down:

=INDEX($A$3:$A$14,I10)

I10:

=MATCH(2,1/($B$3:$B$14=F10))

which is confirmed with control+shift+enter then copied down.

 

[Jon von der Heyden]

Hi Aladin,

Superb solution, thanks!