1. Normal search
2. Cross-look
公式:=INDEX(A:F,MATCH(I9,A:A,0),MATCH(K$8,$1:$1,0))
3. Specify the sum of columns
公式:=SUM(N(INDEX($1:$6,MATCH(D11,A:A,0),N(IF({1},{4,7,9})))))
Because my INDEX is the whole row selection area, I start counting from column A, in column 4 in February, in column 7 in May, and in column 9 in July, the IF {1} and N functions in it are all for dimensionality reduction
4. Multi-region judgment summation
The first parameter of INDEX sets two regions, the area of the first half of the year and the area of the second half of the year, the third parameter determines whether the E10 cell is the first half of the year or the second half of the year, if it is the second half of the year, it returns 2 regions, that is, the data area of the second half of the year, if it is the first half of the year, it returns 1 area, that is, the data area of the first half of the year, and the INDEX two parameters can return the entire row of data in the area without writing
公式:=SUM(INDEX(($C$1:$H$6,$I$1:$N$6),MATCH(D11,A:A,0),,IF(E$10="下半年",2,1)))
5. Calculate the cumulative output
When I select the months of cumulative production, the region is automatically expanded to the column where that month is located, and then the sum is aggregated
FORMULA: =SUM(C7:INDEX($7:$7,MATCH(SUBSTITUTE(F$17,"cumulative",),$1:$1,0)))
6. Data deduplication
公式:=IFERROR(INDEX(A:A,MATCH(0,COUNTIF(E$1:E1,$A$2:$A$8),)+1),"")
The array formula needs to press CTRL+SHIFT+ENTER