I need a formula to get quantity of the last date

[Daniel Valle]

Hi,
I have a table with many parts numbers with a date, some part numbers is repeated, with several dates, but I need to get the quantity of last date

I want to call a specific part number and get the quantity of last date

Example: If I want to pick 627622036B the result must be 20

Part Number Date Qty
6272649W6C 9/15/2015 20
6272649W6C 9/20/2015 25
627379400C 9/15/2015 30
627379400C 9/20/2015 25
627379400C 9/21/2015 20
627622036B 9/15/2015 15
627622036B 9/20/2015 30
627622036B 9/25/2015 15
627622036B 9/26/2015 20
6282010BMD 9/15/2015 35
6282010BMD 9/17/2015 45
6282010BMD 9/20/2015 50

Thank you

 

[Luke M]

Assuming your data is in A:C, and the value you want to lookup is in E1, then the array formula would be:
=INDEX(C:C,MOD(MAX((A2:A100=E1)*B2:B100+(ROW(A2:A100)/10000)),1)*10000)

Confirm the array with Ctrl+shift+enter, not just Enter.

 

[eibi]

Daniel,

Another alternative?

=SUMPRODUCT((A2:A1000=E1)*(B2:B1000=MAX(IF(A2:A1000=E1,B2:B1000)))*(C2:C1000))

Also an array formula (like Luke's) - requires Ctrl+Shift+Enter

See attached.

@Luke M -- Cool way to extract the row number! I appreciate your example.

 

[Daniel Valle]

Luke, thank you very much for your fast response...
So,so,so sorry, I miss something

the database have one more variant that I miss

Ok

A B C D
Part number Date Location Qty
1234 1/1/15 A 10
1234 1/1/15 B 15
1234 2/1/15 C 25
1234 1/1/15 A 10
1234 2/1/15 B 15
1234 3/1/15 C 25
5678 1/1/15 A 10
5678 2/1/15 B 15
5678 3/1/15 C 25
5678 1/1/15 A 10
5678 3/1/15 B 15
5678 7/1/15 C 25
7896 1/1/15 A 10
7896 2/1/15 B 15
7896 2/1/15 C 25
7896 1/1/15 A 10
7896 2/1/15 B 15
7896 2/1/15 C 25

I want to pick part 5678 last date of location B and must be
=15

 

[Luke M]

Thankfully, it's an easy edit.
Let's say that you have the value of "B" in F1.
Array formula:
=INDEX(D:D,MOD(MAX((A2:A100=E1)*(C2:C100=F1)*B2:B100+(ROW(A2:A100)/10000)),1)*10000)

 

[Daniel Valle]

Daniel,

Another alternative?

=SUMPRODUCT((A2:A1000=E1)*(B2:B1000=MAX(IF(A2:A1000=E1,B2:B1000)))*(C2:C1000))

Also an array formula (like Luke's) - requires Ctrl+Shift+Enter

See attached.

Thank you eibi....I reply with some additional info, that I miss in the first question,

Thank you

 

[eibi]

Mine changes accordingly:

=SUMPRODUCT((A2:A1000=E1)*(B2:B1000=MAX((A2:A1000=E1)*(C2:C1000=E2)*B2:B1000))*(C2:C1000=E2)*(D2:D1000))

See attached.

But you'll note that you have to "double enter" the new criteria in mine, as compared with Luke's - I think his is the better alternative.

 

[Khalid NGO]

Hi Daniel,

Glad to join this thread, thought to share what I came up with.
Will this work? :

=INDEX(D2:D19,MATCH(MAX(IF(C2:C19=E2,IF(A2:A19=E1,B2:B19)))&E2,B2:B19&C2:C19,0),)

Array formula, to be enter with CSE.

Regards,