Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup with multiple same name entries | Excel Discussion (Misc queries) | |||
return multiple entries from vlookup | Excel Discussion (Misc queries) | |||
Lookup in Multiple Columns, Return Multiple Values | Excel Worksheet Functions | |||
How to lookup when range contains multiple identical entries? | Excel Discussion (Misc queries) | |||
Lookup with multiple value return | Excel Worksheet Functions |