LOOKUP multiple entries, return last
 

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

--
Thanks!!
Don

LOOKUP multiple entries, return last
 

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.

--
Biff
Microsoft Excel MVP


"Don" wrote in message
...
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 ????

--
Thanks!!
Don

LOOKUP multiple entries, return last
 

Hi,

Here is another solution:

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

where you enter the batch in E1
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Don" wrote:

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

--
Thanks!!
Don

LOOKUP multiple entries, return last
 

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

--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
Hi,

Here is another solution:

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

where you enter the batch in E1
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Don" wrote:

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

--
Thanks!!
Don

LOOKUP multiple entries, return last
 

My dates are not in chronological order so used the MAX(IF statement. Works
perfectly, and fast!
--
Thanks all,
Don


"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
Hi,

Here is another solution:

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

where you enter the batch in E1
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Don" wrote:

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

--
Thanks!!
Don

LOOKUP multiple entries, return last
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Don" wrote in message
...
My dates are not in chronological order so used the MAX(IF statement.
Works
perfectly, and fast!
--
Thanks all,
Don


"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
Hi,

Here is another solution:

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

where you enter the batch in E1
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Don" wrote:

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

--
Thanks!!
Don