# Extract a List of Values Filtered by Criteria with Sub-Arrays (I think???)

#### Briescuis

##### New Member
I have a sheet in a workbook with a lot of data.

If there is an "x" in column B, I want to pull certain data from the row with an x. I tried copying the formula from another site, but it is not quite returning the correct data. The formula I have is below. Any help is much appreciated!!

=IFERROR(INDEX(Affiliations!C\$2:C\$3000,SMALL(IF(Affiliations!\$B\$2:\$B\$3000=\$A\$2,ROW(Affiliations!C\$2:C\$3000)-ROW(Affiliations!C\$2)+1),ROWS(Affiliations!C\$2:Affiliations!C2))),"")

Thank you!!

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
What results are you getting that are different than what you expect? Can you post some sample data, and the expected results?

Column A Column B Column C Column D Column E Column F Column G Column H
101 x DEF Hospital Address State Pending Left VM Agreement

I want it to find any x in column B and pull the data from that row for Column C and Column G.

The result is giving me one further down on my list with no x

Thank you!!

I can't tell from the sample data you posted what row your data starts in, but maybe you need to adjust the formula to begin in row 1, like such:

=IFERROR(INDEX(Affiliations!C\$1:C\$3000,SMALL(IF(Affiliations!\$B\$1:\$B\$3000=\$A\$1,ROW(Affiliations!C\$1:C\$3000)-ROW(Affiliations!C\$1)+1),ROWS(Affiliations!C\$1:Affiliations!C1))),"")

Replies
8
Views
211
Replies
3
Views
273
Replies
3
Views
305
Replies
1
Views
235
Replies
3
Views
243

1,196,368
Messages
6,014,876
Members
441,852
Latest member
ravellig

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