Offset bottom up lookup based on condition

Bering

Board Regular
Joined
Aug 22, 2018
Messages
132
Office Version
  1. 2016
Platform
  1. Windows
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 CColumn E
ABCItem 125
ABCItem 487
DEFItem 78
DEFItem 8
DEFItem 10001
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,474
Office Version
  1. 365
Platform
  1. Windows
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
BCDEF
1Column CColumn E
2ABCItem 125Item 78Item 10001
3ABCItem 487
4DEFItem 78
5DEFItem 8
6DEFItem 10001
Sheet7
Cell Formulas
RangeFormula
E2E2=INDEX($C:$C,AGGREGATE(15,6,ROW($B$2:$B$6)/($B$2:$B$6="DEF"),1))
F2F2=INDEX($C:$C,AGGREGATE(14,6,ROW($B$2:$B$6)/($B$2:$B$6="DEF"),1))
 
Solution

Bering

Board Regular
Joined
Aug 22, 2018
Messages
132
Office Version
  1. 2016
Platform
  1. Windows
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
BCDEF
1Column CColumn E
2ABCItem 125Item 78Item 10001
3ABCItem 487
4DEFItem 78
5DEFItem 8
6DEFItem 10001
Sheet7
Cell Formulas
RangeFormula
E2E2=INDEX($C:$C,AGGREGATE(15,6,ROW($B$2:$B$6)/($B$2:$B$6="DEF"),1))
F2F2=INDEX($C:$C,AGGREGATE(14,6,ROW($B$2:$B$6)/($B$2:$B$6="DEF"),1))

yessss, that's brilliant (y)(y)(y) thank you so much!!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,661
Messages
5,626,155
Members
416,164
Latest member
hamburger138

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top