Oct 06 2021 12:44 AM - edited Oct 06 2021 12:45 AM
Hi team,
I'm having trouble with the INDEX AND MATCH Formula. Here's what I'm trying to achieve. On the attached Workbook, I have two sheets. Sheet "Data" has all the information I want the formula to search from. Sheet "Status" is where I want the results returned to.
I want to search for the email address in Colum A on sheet "Status" and return the training title from Colum C on sheet "Data" for the matching email if the status of the training is 'In progress', 'Not started' or 'Registered'. Hope I'm making sense.
Here's what I cooked up incorrectly: =IF('Data'!C2:C14="In Progress"),INDEX(Data!D2:D12,MATCH(Status!A2,Data!B2:B12,0),2)
Please help
Oct 06 2021 01:11 AM
Solution@lamarsmith You tagged your question with "Excel for Web". Then you can use the FILTER function in conjunction with TEXTJOIN as demonstrated in the attached file. Note that I converted the Data into a structured table (called "tblData"). This makes referencing a lot easier.
Oct 06 2021 02:46 AM
@Riny_van_Eekelen This is brilliant! Thanks a million.
Oct 06 2021 02:53 AM
Just in case, PivotTable with DAX measure
Outstanding training:=CALCULATE (
CONCATENATEX ( tblData, tblData[Training Title], ", " ),
tblData[Transcript Status] <> "Completed"
)
could work.
Shall work in Excel for web if only data model create in desktop version.
Oct 06 2021 03:31 AM
Oct 06 2021 04:19 AM
@lamarsmith , you are welcome
Oct 06 2021 01:11 AM
Solution@lamarsmith You tagged your question with "Excel for Web". Then you can use the FILTER function in conjunction with TEXTJOIN as demonstrated in the attached file. Note that I converted the Data into a structured table (called "tblData"). This makes referencing a lot easier.