I would like to suggest an Array (CSE) Formula, will help you to extract multiple Rows based on few Criteria.
![enter image description here](https://cdn.statically.io/img/i.sstatic.net/u0mBb.png)
How it works:
- I'm assuming that the Source Data is in Range
A2:E10
.
- Criteria Range is
A16:E16
.
- Enter this Formula in Cell
A20
, finish with
Ctrl+Shift+Enter, fill Right then Down.
{=IFERROR(INDEX($A$2:$E$10,SMALL(IF(MMULT(($A$2:$E$10=$A$16:$E$16)*1,{1;1;1;1;1;1;1})=COUNTA($A$16:$E$16),MATCH(ROW($A$2:$A$10),ROW($A$2:$A$10)),""),ROWS($A$1:A1)),COLUMNS($A$1:A1)),"")}
Note:
- If Criteria Cell/Cells will blank, this Formula
will return All records.
![enter image description here](https://cdn.statically.io/img/i.sstatic.net/xa0lD.png)
- You may put one ore more than one Criteria to Filter related Records.
![enter image description here](https://cdn.statically.io/img/i.sstatic.net/dgu2f.png)
Let me explain the mechanism of the Command.
MMULT function
can't work with Boolean values
so in order to get that working, Formula must
multiply the array with 1.
MMULT(($A$2:$E$10=$A$16:$E$16)*1,{1;1;1;1;1;1;1})
becomes,
MMULT({0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 1, 0;0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0;0, 0, 1, 0, 0, 1, 0;0, 0, 1, 0, 0, 0, 0;0, 0, 1, 0, 0, 0, 0;0, 0, 1, 0, 0, 1, 0;0, 0, 1, 0, 0, 0, 0},{1;1;1;1;1;1;1})
and returns,
{0;0;1;0;0;2;1;1;2;1}
and,
MMULT(($A$2:$E$10=$A$16:$E$16)*1, {1;1;1;1;1;1;1})=COUNTA($A$16:$E$16)
becomes,
{0;0;1;0;0;2;1;1;2;1}=COUNTA($A$16:$E$16)
becomes,
{0;0;1;0;0;2;1;1;2;1}=2
and returns,
{FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}.
IF(MMULT(($A$2:$E$10=$A$16:$E$16)*1, {1;1;1;1;1;1;1})=COUNTA($A$16:$E$16), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10)), "")
becomes,
IF({FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}, MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10)), "")
SMALL Function gets the k-th smallest
number in an Array.
INDEX Function returns a value from a cell
Range or Array, based on a Row and Column
number.