Offset bottom up lookup based on condition

[Bering]

Hello, I tried to find similar posts that could help me with this problem but could not find anything. Hope you can help me.

I have some identifiers in column C and my lookup values in column E.

I am using this formula to look up from the bottom: LOOKUP(2,1/('Call Distri PE funds'!C:C="DEF"),'Call Distri PE funds'!E:E)

The same identifiers could have 1 or more items associated to it; what I would like to achieve is to offset the above formula to find the first item before the identifiers changes:

LOOKUP(2,1/('Call Distri PE funds'!C:C="DEF"),'Call Distri PE funds'!E:E) result = Item 10001
Offset the same formula in some way to get result = Item 78

In case this could be relevant, the same identifiers can appear multiple times in column C.

Many thanks in advance

Column C	Column E
ABC	Item 125
ABC	Item 487
DEF	Item 78
DEF	Item 8
DEF	Item 10001

 

[jasonb75]

Like this?

Note that the only difference in the 2 formulas is the first number in the AGGREGATE function, 14 starts from the bottom, 15 starts from the top.
If you wanted to find 2nd from bottom or second from top then you would simply need to change the 1 at the end to 2.

Book1 (version 1).xlsb
	B	C	D	E	F
1	Column C	Column E
2	ABC	Item 125		Item 78	Item 10001
3	ABC	Item 487
4	DEF	Item 78
5	DEF	Item 8
6	DEF	Item 10001
Sheet7

Cell Formulas
Range	Formula
E2	E2	=INDEX($C:$C,AGGREGATE(15,6,ROW($B$2:$B$6)/($B$2:$B$6="DEF"),1))
F2	F2	=INDEX($C:$C,AGGREGATE(14,6,ROW($B$2:$B$6)/($B$2:$B$6="DEF"),1))

 

[Bering]

Like this?

Note that the only difference in the 2 formulas is the first number in the AGGREGATE function, 14 starts from the bottom, 15 starts from the top.
If you wanted to find 2nd from bottom or second from top then you would simply need to change the 1 at the end to 2.

Book1 (version 1).xlsb
	B	C	D	E	F
1	Column C	Column E
2	ABC	Item 125		Item 78	Item 10001
3	ABC	Item 487
4	DEF	Item 78
5	DEF	Item 8
6	DEF	Item 10001
Sheet7

Cell Formulas
Range	Formula
E2	E2	=INDEX($C:$C,AGGREGATE(15,6,ROW($B$2:$B$6)/($B$2:$B$6="DEF"),1))
F2	F2	=INDEX($C:$C,AGGREGATE(14,6,ROW($B$2:$B$6)/($B$2:$B$6="DEF"),1))

yessss, that's brilliant thank you so much!!