I need help with this Excel question, since I have tried many things and formulas, but I could not solve the problem.

enter image description here

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.

closed as off-topic by n8te, bertieb, fixer1234, music2myear, Overmind Sep 20 at 11:24

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions seeking product, service, or learning material recommendations are off-topic because they become outdated quickly and attract opinion-based answers. Instead, describe your situation and the specific problem you're trying to solve. Share your research. Here are a few suggestions on how to properly ask this type of question." – bertieb, Overmind
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 3
    Hello Frankito, and welcome to Superuser! You might not be aware that this site is about learning, as opposed to have someone else literally do your homework. If you show your efforts and where you got stuck, then more experienced users will very likely point you in the right direction. – simlev Sep 14 at 13:35