Hello,
I am wondering is it possible to use INDEX, SMALL, MATCH, ROW formula combination to extract all rows which contain a name from named range.
For example, there are two name range (let it be person a) (names from range T3:T30, and person b) (names from range U3:U27).
I have yet extracted rows which meet pre-criteria, so I now want to extract all rows in two tables each one for a named range for person a names, and person b names
For example let it be person a is John, and person b is Steven. Each one have a collaborators which whom they work and guide some data for them.
The extracted data lays in range B10:H90. I want to extract all rows in new table for person a (with names from his named range, and same for person b.
I tried to make named ranges with name John and Steven, and than make in cells G4 and G5 data validation to this named ranges but I get always errors.
=INDEX($A$10:$H$90;SMALL(IF(($G$4<=$A$10:$A$90)*($G$5>=$A$10:$A$90);MATCH(ROW($A$10:$H$90);ROW($A$10:$H$90)));ROW(A1));COLUMN(A9))
getting #NUM, and if I try to put named range in formula (in place of G4 and G5, I got # REF error.
Also I want to use this worksheet as shared (two pc) so any macro or table formatted isn't good idea.
Any advice is appreciated.
Bookmarks