Layered Search Queries in Excel

Question

ABC 1 PROD1 TYPE1 VAL1 2 PROD2 TYPE1 VAL2 3 PROD1 TYPE2 VAL3 4 PROD2 TYPE3 VAL2 

In an empty cell, I want to get the value in column C for Prod Type = Prod2 and type = type3.

I will be grateful for any help.

Answer 1

Take a look at using the DGET Excel function.

Set

• A1 = ProdType
• B1 = Type
• C1 = Val

Then your submitted data in A2: C5

Then

• H1 = ProdType
• I1 = Type
• H2 = =PROD2 (criterion 1)
• I2 = =TYPE3 (Criteria 2)

And finally, in H3:

 =DGET(A1:C5,"Val",H1:I2) 

This should get meaning to you.

Answer 2

 =SUMPRODUCT(($A$1:$A$4="PROD2")*($B$1:$B$4="TYPE3")*($C$1:$C$4)) 

This assumes that the values ​​of column C are actually numbers, not text.

Answer 3

Improvement to Dick Kuslayka's solution for the case when the output column does not contain numbers,

 =INDEX(C:C,SUMPRODUCT((A:A="PROD2")*(B:B="TYPE3")*ROW(C:C)),0) 

Taken from here .

See also this answer .