Return all values of an array with conditions

Navigation
1

I'm trying to get a list of products from 2 values in excel. I have seen in this tutorial that I can make a formula of the following way: 

={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 a value, but I need to use two values for the search, so I put another SE() to do this (I'm using Office 2010 and I do not have SES() ) My role is as follows: 

={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 was not working, so when I debugged I realized that it gave me the error #NÚM! for the following reason: When it enters the second SE() it loses the information of the numbers of SE() previous and turns them into FALSE. So how do I fix this? Is there another formula I can use for this? Thankful.

    
asked by anonymous 12.11.2016 / 02:48

1 answer

1

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

Let column "A" contain "Value 1" which is the customer code and column "B" or "Value 2" which is the product code, create in column "C" or "Value 3 "with the line number + 1 of this customer's instance for this product, for example:

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

Line 5 column B, "Value 2" = 987

Line 5 column C, "Value 3" = 6

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

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

I have applied this type of solution for similar cases.

    
13.11.2016 / 02:40
Resize image to screen size How to include content from a page .py on another page .py