Extract rows if names are meet names in named range

serggio

New Member
Joined
Nov 12, 2014
Messages
9
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.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Forum statistics

Threads
1,144,155
Messages
5,722,818
Members
422,460
Latest member
VBA_Noob01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top