# Copy Ranges Meeting Criteria

#### schoenebergercms

##### New Member
I have a worksheet with school numbers and test types in a vertical file, such as
schl1 Math3
schl2 Math3
schl3 Math3
schl1 Math4
schl2 Math4
schl10 Math8

I want to create a new worksheet that contains a list of the school numbers with Math3. Then a new worksheet that contains a list of the school numbers with Math4, etc.

Can someone help?

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Try this in each sheet. You will have to replace Sheet1 with the actual name of your master sheet. Keep the ! sign at the end. You may have to adjust the ranges to suit your master sheet size. Also, for each sheet, change the 2 occurences of Math3 with the respective lookup string.

=IF(ROW(sheet1!\$A2)-ROW(sheet1!\$A\$1)>COUNTIF(Sheet1!\$B\$1:\$B\$100,"Math3"),"",INDEX(Sheet1!\$A\$1:\$A\$100,SMALL(IF(Sheet1!\$B\$1:\$B\$100="Math3",ROW(Sheet1!\$A\$1:\$A\$100)),ROW(sheet1!\$B2)-ROW(Sheet1!\$B\$1))))

You must confirm the formula with Ctrl+Shift+Enter, not just Enter.

And then copy down as far as you want.

Replies
4
Views
137
Replies
1
Views
78
Replies
21
Views
455
Replies
11
Views
415
Replies
12
Views
326

1,206,921
Messages
6,075,590
Members
446,147
Latest member
homedecortips

### 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.

### Which adblocker are you using?

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

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