Only show results where condition is met

[Chibikitsuneluna]

Hi, I am new here and unfortunately due to restrictions and data protection I can't post a mini sheet or even screenshot my workbook. I am willing to provide any/all data I can to help get an answer. I have attached images of the Headers but am unable to provide much more unfortunately.


I have a very large Workbook; where my main Worksheet, named Caseload, which uses a Table.

I have another Worksheet, named Andrea; where I only want to pull Data relevant to this person.

The result I need in Worksheet Andrea, column A; is to return the CRN (column B) from Worksheet Table Caseload; only if the Appt Therapist (column AJ) in Caseload says "Andrea".


The current formula in the Andrea Worksheet that I am using in A3 is:

=IFERROR(INDEX('CEREDIGION CASELOAD'!$B$2:$B$2000,SMALL(IF(('CEREDIGION CASELOAD'!$AJ$2:$AJ$2000=$A$1),ROW('CEREDIGION CASELOAD'!$AJ$2:$AJ$2000)-ROW('CEREDIGION CASELOAD'!$AJ2)+1),ROWS(AJ$2:AJ2))),"")



This returns the first CRN from Caseload, where the Appt Therapist in Caseload is Andrea; however it fails after that, returning seemingly random results from the list; and even returning some of these random results multiple times.

Any help will be much appreciated!!! Thanks!

 

[Fluff]

How about

=FILTER('CEREDIGION CASELOAD'!$B$2:$B$2000,'CEREDIGION CASELOAD'!$AJ$2:$AJ$2000=$A$1,"")

 

[Chibikitsuneluna]

How about

=FILTER('CEREDIGION CASELOAD'!$B$2:$B$2000,'CEREDIGION CASELOAD'!$AJ$2:$AJ$2000=$A$1,"")

Thanks, the data shown is correct, HOWEVER it doesn't start until A75, before that it just shows #SPILL!

Any ideas?

 

[Fluff]

You need to put the formula in a single cell (do not filldown) & clear all cells below the formula.

 

[Chibikitsuneluna]

Thank you so much - this has been such a headache for me!!

 

[Fluff]

You're welcome & thanks for the feedback.