Index Match to find previous occurrence and call the second latest occurrence to a new cell

[Razark88]

Hello,

I m trying to find a way to pull the second newest revision to a cell. I got the first part of my issue , where indexing was used to find all previous occurrence( i found it out from another older thread in the forum) . Can someone help me find a way to call the the second latest revision to another cell.

The first image shows the part numbers from 1 to 4 having varying revision from A.1 to D.1

The second image shows the formula I have used to find the previous revision number of each part and show it against the Part number. ( Formula used to find previous occurrence = =IFERROR(INDEX($B$2:B3,AGGREGATE(14,6,(ROW($A$2:A3)-ROW($A$2)+1)/($A$2:A3=A3),2)),"") )

What I am looking for now is to find a way to pull the second latest revision automatically to another cell . (for example if we look at Part number "1", the latest revision is D.1, I want to figure out how to pull revision C.1 to another cell using a formula )

can anyone help please ?


(PS: as mentioned the formula is from an older thread of similar name made by user Willow1985 )

 

[Razark88]

Formula Test.xlsx
	A	B	C	D	E
1	Part Number	Rev	Previous Rev- formula 1	Previous Rev- formula 2	Rev before the latest Rev
2	1	A.1			I need to find a formula which will pull the revision before the latest one, i.e for Part Number 1, latest revision is E.1 , what can I do to pull D.1 as a result out of all 5 revision ?
3	2	A.1
4	3	A.1
5	4	A.1
6	1	B.1	A.1	A.1
7	2	B.1	A.1	A.1
8	3	B.1	A.1	A.1
9	4	B.1	A.1	A.1
10	1	C.1	B.1	B.1
11	2	C.1	B.1	B.1
12	3	C.1	B.1	B.1
13	4	C.1	B.1	B.1
14	1	D.1	C.1	C.1
15	2	D.1	C.1	C.1
16	3	D.1	C.1	C.1
17	1	E.1	D.1	D.1
PN Revision

Cell Formulas
Range	Formula
C2:C17	C2	=IFERROR(INDEX($B$2:B2,AGGREGATE(14,6,(ROW($A$2:A2)-ROW($A$2)+1)/($A$2:A2=A2),2)),"")
D2:D17	D2	=IFERROR(LOOKUP(2,1/($A$1:$A1=A2),$B$1:$B1),"")

 

[Fluff]

Hi & welcome to MrExcel.
How about

=IFERROR(INDEX($B$2:B$20,AGGREGATE(14,6,(ROW($A$2:A$20)-ROW($A$2)+1)/($A$2:A$20=A2),2)),"")