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

4653

New Member
Joined
Apr 20, 2012
Messages
27
Office Version
  1. 365
Platform
  1. Windows
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/ColumnABC
14E4
24R
34T
44J

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.
 

Some videos you may like

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
Joined
Dec 30, 2008
Messages
12,399
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows
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’)

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

4653

New Member
Joined
Apr 20, 2012
Messages
27
Office Version
  1. 365
Platform
  1. Windows
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’)

How about
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
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows
Glad we could help, thanks for the feedback & updating your profile.
 

Watch MrExcel Video

Forum statistics

Threads
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.
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