Return all values of a matrix with conditions

Question

I’m trying to get a list of products from 2 values in excel. Vi in this tutorial I can make a formula as follows:

={SEERRO(ÍNDICE($A$6:$B$15;MENOR(SE($A$6:$A$15=$E$5;LIN($A$6:$A$15)-5);LIN(A1));2);"")}

This is the solution if I were to search from one value, but I need to use two values for the search, so I put another SE() to do this (I am using Office 2010 and do not have the SES()) My job is this::

={SEERRO(ÍNDICE('Materiais Usados'!$A$3:$G$27;MENOR(SE('Materiais Usados'!$A$3:$G$27=$A$26;SE('Materiais Usados'!$A$3:$G$27=$B$1;LIN('Materiais Usados'!$A$3:$G$27)-2));LIN(A1));1);"")}

It wasn’t working, so when I deposed him I realized he was giving me the error #NÚM! for the following reason: When he enters the second SE() he loses the information of the numbers of SE() previous and turn them into FALSE. So how do I fix this? Is there another formula I can use for this? Thank you.

Answer 1

The problem can be solved like this (in an unconventional way):

Let’s say that column "A" contains the "Value 1" which is the customer code and column "B" the "Value 2" which is the product code, create in column "C" the "Value 3" with the line number + 1 of the occurrence of this customer for this product, for example:

Row 5 column A, "Value 1" = 15 (client 15)

Row 5 column B, "Value 2" = 987 (product 987)

Row 5 column C, "Value 3" = 6 (row number + 1 in cell: LIN()+1)

In the results table create an additional column to record the starting line of the next search for the same client, for example:

Código do Cliente: 15

Produto   Linha inicial
          2
987       6
1021      197
1533      202
-         -
-         -
-         -
-         -

Note that the first cell of the "Product" column row is blank, and should remain blank, but the respective cell next to the "Home" column is set to 2, this means that every search will start from the second row (assuming that line 1 has the header).

Thus, the first formula should start the search "only for the same client code (15)" from the next upper row by the cell value of the "Start line" column, for example, the first search will start from line 2 and this client (15) will be found in line 5 with the respective product code 987, then will be registered the number of the next line "starting the new search", line 6 (5 + 1).

In the next row formula for the search "only for the same client code", it should be started from line 6 - which will be taken from the "Starting line" column of the top line that presented the first occurrence - and will result in the next occurrence in line 196 with product 1021, which in turn will indicate the next line to start the search, to 197 (196 + 1), and so it must be done successively.

The formula should consider that if there is no occurrence (client not found from the initial line of the respective line at the time of the search, the result will be "white" (double quotes, for example) or as shown "a dash" ("-").

I’ve applied this type of solution to similar cases.