Search code examples
excelselectexcel-formularowwhere-clause

How to SELECT FROM WHERE in excel? Return rows from database

I want to return rows from a table, where certain conditions are met, in excel.

ProdGroup   Width (mm)  Diameter (mm)   Date
Prod1           1120            1000   2016-01-11
Prod2           600             1000   2017-10-18
Prod1           930             800    2015-04-11
Prod3           1250            1200   2016-04-18
Prod2           840             1000   2019-06-27
Prod2           840              900   2018-03-21

I want to be make an equivalent of: "SELECT * FROM Table WHERE ProdGroup = "Prod2" AND Diameter = 1000" in excel. My idea is that I enter the values into two cells and that rows are returned based on what I write in the two cells.

I have tried using the =INDEX() function but I only managed to search for rows matching only 1 condition. Furthermore, I only succeeded return one row. 

=INDEX(B2:D6,MATCH(A10,A2:A6,0),1)

This only gives me one IN parameter. With Prod2 this would only return one row.

With inputs "Prod2" and 1000:

ProdGroup         Diameter
Prod2             1000

I want an outcome like this:

Prod2           600             1000            2017-12-18
Prod2           840             1000            2019-06-27

I have no idea on how to go about doing this. Can somebody please help?

Kind regards pbdude

Solution

  • Here is an option:

    enter image description here

    Formula in I2:

    =IFERROR(INDEX(A$1:A$7,SMALL(IF((($A$2:$A$7=$F$2)*($C$2:$C$7=$G$2))>0,ROW($A$2:$A$7),""),ROW(1:1))),"")

    Confirm through CtrlShiftEnter

    Drag down and right...