SOLVED

IF FUNCTION WITH INDEX AND MATCH TO RETURN MULTIPLE VALUES

Copper Contributor

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

 

5 Replies
best response confirmed by lamarsmith (Copper Contributor)
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.

@Riny_van_Eekelen This is brilliant! Thanks a million.

@lamarsmith 

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
1 best response

Accepted Solutions
best response confirmed by lamarsmith (Copper Contributor)
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.

View solution in original post