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

[4653]

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.

 

[jasonb75]

Try

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

 

[Fluff]

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

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

 

[4653]

Try

=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]

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

=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]

Glad we could help, thanks for the feedback & updating your profile.