I need help with this Excel question, since I have tried many things and formulas, but I could not solve the problem.
The question is explained in the image. However, I reproduce the related text below:
In this range of cells (Ex: range D6:D8) of Sheet-1 the expected formula must be written which will have to return the result according to the following explanation:
Question about the complex function to be adopted in the RANGE D6:D8 (column "D") of various sections named Product-X, Y, Z, ecc (Sheet-1), and composed by the following three steps:
Step-1: (Sheet-1) If the cell $D$4 (Production date: dd:mm:yy) is different from blank, compare the exact text of the individual cells of range C6:C8 (Raw materials) with those of the merged cell of range H6:H11 of column "H" in Sheet-2;
Step-2: If the texts of single cell will be exact, the function will compute the correspondent adjacent range of cells (Ex: "K6:M7" - Sheet-2) as follows Step-3:
Step-3: in the corresponding range (Ex: K6: M6) searches for the minor date compared to the one in ex. $D$4 (Sheet-1) and then returns the value of vertical adjacent cell of the row "Beath code" .
Important: In column N (Sheet-2) I have composed an example of the individual actions of this last Step-2/3. The function have the [match-type] = 1: If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. The lookup_array must be sorted in ascending order.