Extract Multiple Unique, Dynamic Lists from Matching Criteria [SOLVED]

[jdppep]

I am trying to extract multiple dynamic lists from a range matching certain criteria.


I need to extract from the "Providers" sheet unique, dynamic lists without blanks to "Clinic" sheet by matching Row 1 on "Clinic" to column E on "Providers"


Here is an example:


https://docs.google.com/spreadsheet/ccc?key=0Atek2wjie2YLdDZ2ZmYtVVFpY0dBVDM0aWlmR19wdkE&usp=sharing

 

[Faseeh]

Hi jdppep,


Enter this in 'Clinic' Cell A2, press ctrl+shift+enter and drag down/right:

=IFERROR(INDEX(Providers!$B$3:$B$10,SMALL(IF(A$1=Providers!$E$3:$E$10,ROW(Providers!$E$3:$E$10)),ROW(A1))-2,0),"")

Regards,

 

[jdppep]

Hi Faseeh,


This came very close. In my live database, I have 46 clinics. Clinic1 was skipped, Clinic2 returned 4/7 entries, Clinic3 was blank (accurately), and Clinic4 returned 3/4.


I used ctrl+shift+enter and dragged as you described. Any thoughts on why this did not work?

 

[jdppep]

Faseeh,


I used named ranges and the formula you provided, made a small modification and this worked:


=IFERROR(INDEX(NPI_Providers,SMALL(IF(A$1=ClinicList_Providers,ROW(ClinicList_Providers)),ROW(A1))-3,0),"")


Thank you!!