# Lookup 2nd Occurrence of Value Starting From Bottom Row And Return Value In Different Column

#### 4653

##### New Member
I'm looking for a formula that will look in Column A:A and match the 2nd occurrence starting from the bottom row and looking up, looking for a value located in C1. It will then need to return the value in the same row as the 2nd occurrence in Column B. For instance:
 Row/Column A B C 1 4 E 4 2 4 R 3 4 T 4 4 J

In my above example, it would look for the value in C1 which is 4. It would start from the bottom of Column A to find the 2nd occurrence starting from the bottom and looking upward. The 2nd occurrence starting from the bottom is in Row 3 so it would return a value of T which is in B3. Column A will have a bunch of different values, some repeating, some not. I just used the same value of 4 for this example for the sake of ease.

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### jasonb75

##### Well-known Member
Try

Excel Formula:
``=IFERROR(INDEX(B:B,AGGREGATE(14,6,ROW(\$A\$1:\$A\$4)/(\$A\$1:\$A\$4=\$C\$1),2)),"No Match")``

#### Fluff

##### MrExcel MVP, Moderator
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Excel Formula:
``=INDEX(B1:B40,AGGREGATE(14,6,(ROW(B1:B40)-ROW(B1)+1)/(A1:A40=C1),2))``

#### 4653

##### New Member
Try

Excel Formula:
``=IFERROR(INDEX(B:B,AGGREGATE(14,6,ROW(\$A\$1:\$A\$4)/(\$A\$1:\$A\$4=\$C\$1),2)),"No Match")``
Awesome! This works perfect. Thanks so much.

#### 4653

##### New Member
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Excel Formula:
``=INDEX(B1:B40,AGGREGATE(14,6,(ROW(B1:B40)-ROW(B1)+1)/(A1:A40=C1),2))``
This works great! Thanks so much. And thanks for the tip. I just updated my Account Details. I didn't even think about that.

#### Fluff

##### MrExcel MVP, Moderator
Glad we could help, thanks for the feedback & updating your profile.

Replies
1
Views
237
Replies
2
Views
110
Replies
14
Views
108
Replies
7
Views
158
Replies
15
Views
801

1,126,944
Messages
5,621,753
Members
415,854
Latest member
Tutu123

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