Forum Discussion
IF FUNCTION WITH INDEX AND MATCH TO RETURN MULTIPLE VALUES
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
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.
5 Replies
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.
- Thanks! This is helpful
lamarsmith , you are welcome
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.
Riny_van_Eekelen This is brilliant! Thanks a million.
