Last number lookup help

DarrenF

Board Regular
Joined
Jun 9, 2014
Messages
90
Hello,

In column B I have case numbers and some of them are duplicate. I need to have it find the last duplicate number (furthest down in the column) and have it return the corresponding name associated to that row in Column H.

Example:

If I put the formula in cell I2, number 14019509 would need to return the name Mo in cell I2. Dragging the formula down, I3 should show Mo, I4 should show Mo and so on down the list.

Column BColumn H
14019509Larry
14019509Curly
14019509Mo
14030761Jimmy
13974860Larry
13980510Curly
14034425Mo
13930488Jimmy
13930488Larry
13934390Curly
13934390Mo
13924003Jimmy
13924003Larry
13924003Curly
13924003Mo
13970137Jimmy
13970137Larry
14045315Curly
14051012Mo
13966373Jimmy

<colgroup><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
How about
=LOOKUP(2,1/($B$2:$B$21=B2),($H$2:$H$21))
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
In column B I have case numbers and some of them are duplicate. I need to have it find the last duplicate number (furthest down in the column) and have it return the corresponding name associated to that row in Column H.
Example:
If I put the formula in cell I2, number 14019509 would need to return the name Mo in cell I2. Dragging the formula down, I3 should show Mo, I4 should show Mo and so on down the list.

Hi,

Just curious, when there's no "duplicate" for the case number, do you Still want the name shown?

Assuming your sample data starts in B2:B21, there's No duplicates for rows 5, 6, 7, 8, 19, 20, 21
 
Upvote 0
Great question, yes. I still need to have it return a name. In that case, its the first and last.
 
Upvote 0
Great, then you already have the solution from Fluff.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

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
Back
Top