LOOKUP multiple entries, return last

  • Thread starter Thread starter Don
  • Start date Start date
D

Don

I have a worksheet listing batch shipment information by qty and date. There
are 12,000 rows. The same batch can/will be shipped on multiple dates, or the
same date.

Supporting worksheet
A B C
1 Batch Qty Date
2 1111 12 1-1
3 1111 10 1-1
4 3333 5 1-1
5 2222 10 1-5
6 1111 4 1-5
7 3333 13 1-8
8 2222 5 1-8


I can use VLOOKUP to return the First date each batch was shipped.
What can I use to return the Last date found in the worksheet for each batch
shipped?


Primary worksheet
A B C
1 Batch First Date Last Date
2 1111 1-1 ????
3 2222 1-5 ????
4 3333 1-1 ????
 
One way...

Assuming the last date means the most recent date.

Array entered** :

=MAX(IF(Sheet2!A2:A12000=A2,Sheet2!C2:C12000))

Format as Date

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
Hi,

Here is another solution:

=LOOKUP(1,--(E1=A1:A12000),C1:C12000)

where you enter the batch in E1
 
=LOOKUP(1,--(E1=A1:A12000),C1:C12000)

That can return an incorrect result.

Try it like this:

=LOOKUP(2,1/(A1:A12000=E1),C1:C12000)

That assumes the dates for the batch number are in chronological ascending
order.

However, the MAX(IF(...)) array is faster to calculate.
 
My dates are not in chronological order so used the MAX(IF statement. Works
perfectly, and fast!
 
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


Don said:
My dates are not in chronological order so used the MAX(IF statement.
Works
perfectly, and fast!
Click to expand...
 
Back
Top