Hello,

I am trying to write a formula that will search a large data field for a given office number and return a list of name from that office if they subscribe to a program. I have included a very basic version of what I am trying to do as an example. In this example the array would search for the value in I4 in column B and check that Column D is "Yes" and return a list of names that meet those two criteria.

The data in the real spread sheet is quite a bit more and has over 10k rows and around 100 columns. This is the formula I have started but it is returning a Name error. Also it is only looking at the office number and I have not included the subscriber requirement yet.

=IFERROR(INDEX('Total Data'!$H$1:$H$9120, SMALL(IF('Total Data'!$B$9:$B$9120= VLOOKUP('HO Data Results'!B8, MP_Table, 2,FALSE), ROW('Total Data'!$H$1:$H$9120)-ROW('Total Data'!$H$8)+1), ROWS('Total Data'!$H$8:'Total Data'!$H$8))),””)

If I run the formula not as an array it will return one name, if I run it as an array I get a #Name? error.

Thanks for the help!Example.xlsx