index and match or vlookup for two sheets

Question

Hello,
I have to resolve the following question in excel.
So I ask you for help.
Many thanks....

See in the Attachment

Explanation:
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 of column "C" of various sections of Product-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 C5:C7 (Raw material) with those of the merged cell of range H6:H11 of the "H" column 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:

- in the corresponding range (Ex: k6: M6) searches for the minor date compared to the one in $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.
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.

I await your kind reply.

Frank Man VLOOKUP FOR TWO SHEET_(Extend-Offie).pdf VLOOKUP FOR TWO SHEET_(Extend-Offie).pdf

두 시트에 대한 색인 및 일치 또는 vlookup