Formula how to add the two quantity from the earliest date

user_16331568 0
Viewed 60

I want to look for the nearest exp date 'Cell G" and quantity 'Cell H' from the left data. With refer to the sample screenshot,

  1. to fine the nearest exp date: I using xlookup to return earliest date by matching with the material. Return value 04-Mar-22.

  2. to find the nearest exp quantity: if using xlookup will only return the first quantity which is 7. But, actually there are two line with same batch D261205L and same exp date 04-Mar-22, is there any formula can get the result of adding that two quantity?

Sample

excel-formula
1 Answers

enter image description here

First, lookup the earliest date for the material

=XLOOKUP(F2,A2:A9,D2:D9,"")

which looks up the value F2 (in this case m2) in the range A2:A9 and returns the corresponding value from the range D2:D9. This goes into cell G2.

In cell H2, write

=SUM(INDEX(FILTER(A2:D9,(D2:D9=G2)*(A2:A9=F2),""),,3))

which filters the range A2:D9 for multiple conditions, the date and then the material. The INDEX function slices the returned array for the qty column (which is column 3 in A2:D9), and the SUM function then sums the filtered column.

Then wrap the whole thing up with IFERROR

=IFERROR(SUM(INDEX(FILTER(A2:D9,(D2:D9=G2)*(A2:A9=F2),""),,3)),"")

which returns the empty string if any of the criteria aren't present.

user_43619990
Related