Last number lookup help

[DarrenF]

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.

[TABLE="width: 214"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Column B[/TD]
[TD]Column H[/TD]
[/TR]
[TR]
[TD]14019509[/TD]
[TD]Larry[/TD]
[/TR]
[TR]
[TD]14019509[/TD]
[TD]Curly[/TD]
[/TR]
[TR]
[TD]14019509[/TD]
[TD]Mo[/TD]
[/TR]
[TR]
[TD]14030761[/TD]
[TD]Jimmy[/TD]
[/TR]
[TR]
[TD]13974860[/TD]
[TD]Larry[/TD]
[/TR]
[TR]
[TD]13980510[/TD]
[TD]Curly[/TD]
[/TR]
[TR]
[TD]14034425[/TD]
[TD]Mo[/TD]
[/TR]
[TR]
[TD]13930488[/TD]
[TD]Jimmy[/TD]
[/TR]
[TR]
[TD]13930488[/TD]
[TD]Larry[/TD]
[/TR]
[TR]
[TD]13934390[/TD]
[TD]Curly[/TD]
[/TR]
[TR]
[TD]13934390[/TD]
[TD]Mo[/TD]
[/TR]
[TR]
[TD]13924003[/TD]
[TD]Jimmy[/TD]
[/TR]
[TR]
[TD]13924003[/TD]
[TD]Larry[/TD]
[/TR]
[TR]
[TD]13924003[/TD]
[TD]Curly[/TD]
[/TR]
[TR]
[TD]13924003[/TD]
[TD]Mo[/TD]
[/TR]
[TR]
[TD]13970137[/TD]
[TD]Jimmy[/TD]
[/TR]
[TR]
[TD]13970137[/TD]
[TD]Larry[/TD]
[/TR]
[TR]
[TD]14045315[/TD]
[TD]Curly[/TD]
[/TR]
[TR]
[TD]14051012[/TD]
[TD]Mo[/TD]
[/TR]
[TR]
[TD]13966373[/TD]
[TD]Jimmy[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

How about
=LOOKUP(2,1/($B$2:$B$21=B2),($H$2:$H$21))

 

[DarrenF]

that worked perfect, thank you!!!

 

[Fluff]

You're welcome & thanks for the feedback

 

[jtakw]

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

 

[DarrenF]

Great question, yes. I still need to have it return a name. In that case, its the first and last.

 

[jtakw]

Great, then you already have the solution from Fluff.