selecting last values in a list

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,806
Office Version
  1. 365
Platform
  1. Windows
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
BCDEFG
2PeriodCodeRateSum of RatePeriod
3Aug-051-16CodeOct-05
4Aug-052-171-24
5Aug-054-182-25
6Sep-051-194-26
7Sep-052-205-27
8Sep-053-21
9Sep-054-22
10Sep-055-23
11Oct-051-24
12Oct-052-25
13Oct-054-26
14Oct-055-27
Sheet1
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
noj said:
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
ABCDEFGHI
1
2PeriodCodeRateCodeLast RatePeriod
3Aug-051-161-24Oct-05
4Aug-052-172-25Oct-05
5Aug-054-183-21Sep-05
6Sep-051-194-26Oct-05
7Sep-052-205-27Oct-05
8Sep-053-21
9Sep-054-22CodeLast RatePeriodPos
10Sep-055-231-24Oct-059
11Oct-051-242-25Oct-0510
12Oct-052-253-21Sep-056
13Oct-054-264-26Oct-0511
14Oct-055-275-27Oct-0512
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,530
Messages
5,572,670
Members
412,481
Latest member
nhantam
Top