Worksheets Involved:
Column A of worksheet 'Key Issue Details' contains a list of exam titles
Column C of worksheet 'Key Issue Details' contains a list of corresponding exam types (Audit, Credit Review, etc.)
Cells A20:A22 of worksheet 'PickList' contain all of the possible exam types found in column C of 'Key Issue Details'
Column C of worksheet 'Calendar & Exam Summary' (cells A5:A16), contains an empty list for all Audit type exams
I want to enter a formula into cells A5:A16 on worksheet 'Calendar & Exam Summary' that will copy over all exam titles from 'Key Issue Details' column A, but only if 'Key Issue Details' column C matches any of the cells A20:A22 in 'PickList'.
I am using the formula below which does retrieve this information, however instead of copying them over one after the other in Column C ('Calendar & Exam Summary'), it is matching row for row. For example:
Formula being used: =IFERROR(INDEX('Key Issue Details'!$A$2:$B$2,MATCH(PickList!$A$20,'Key Issue Details'!$C$2:$D$2,0)),"")
'Calendar & Exam Summary' ...........'Key Issue Details'
C5 - ...................................C2 - Exam A (doesn't match PickList)
C6 - Exam 1.......................C3 - Exam 1 (Meets Criteria)
C7 - ...................................C4 - Exam B (doesn't match PickList)
C8 - Exam 2.......................C5 - Exam 2 (Meets Criteria)
C9 - Exam 3........................C6 - Exam 3 (Meets Criteria)
I would like it to look like this:
Calendar & Exam Summary ........ Key Issue Details
C5 - Exam 1........................C2 - Exam A (doesn't match PickList)
C6 - Exam 2........................C3 - Exam 1 (Meets Criteria)
C7 - Exam 3........................C4 - Exam B (doesn't match PickList)
C8 - .....................................C5 - Exam 2 (Meets Criteria)
C9 - .....................................C6 - Exam 3 (Meets Criteria)
Solved by T. S. in 18 mins