# Offset bottom up lookup based on condition

#### Bering

##### Board Regular
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.

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

### 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
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))

#### Bering

##### Board Regular
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 thank you so much!!

Replies
7
Views
192
Replies
1
Views
79
Replies
5
Views
194
Replies
0
Views
88
Replies
5
Views
143

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.

### Which adblocker are you using?

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

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