# Custom Look Up ?

#### JonathanRichardson

##### New Member
I have two columns, one is “job numbers” with a eight digit number, the other has “Status date time” with the format as “13/09/2006 16:15:00”

My problem is that the job numbers show each time the status changes. So the “job numbers” column has 6278 rows but only 1400 unique jobs with 6278 status changes.

I want to end up with 1400 rows showing the date and time of the last status change?

Job Number Status date time
66397053 13/09/2006 16:15
66397053 12/09/2006 16:18
66397053 05/09/2006 15:48
66397053 29/08/2006 15:08
68210473 03/08/2006 13:00
68210473 02/08/2006 02:21
68210473 01/08/2006 05:28
68283571 25/07/2006 13:48
68283571 24/07/2006 11:28
68283571 23/07/2006 08:00

Example

Job Number Status date time
66397053 13/09/2006 16:15
68210473 03/08/2006 13:00
68283571 25/07/2006 13:48

Thanks

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Hi,

Try:

D1: =INDEX(A2:\$A\$100,MATCH(0,--ISNUMBER(MATCH(A2:\$A\$100,\$D\$1:D1,0)),0))

E1: =MAX(IF(\$A\$2:\$A\$100=D2,\$B\$2:\$B\$100))

Both confirmed with Ctrl + shift + enter before being dragged down.
Book1
ABCDE
1Job NumberStatus date timeJob NumberStatus date time
26639705302/09/06 16:156639705302/09/06 16:18
36639705302/09/06 16:186821047302/08/06 13:00
46639705302/09/06 15:486828357102/07/06 13:48
56639705302/08/06 15:08
66821047302/08/06 13:00
76821047302/08/06 2:21
86821047302/08/06 5:28
96828357102/07/06 13:48
106828357102/07/06 11:28
116828357102/07/06 8:00
Sheet2

Replies
3
Views
433
Replies
1
Views
401
Replies
13
Views
549
Replies
1
Views
96
Replies
2
Views
389

1,217,328
Messages
6,135,917
Members
449,972
Latest member
Natejack

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

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