SOLVED

New Contributor

# 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)

5 Replies
best response confirmed by lamarsmith (New Contributor)
Solution

# Re: IF FUNCTION WITH INDEX AND MATCH TO RETURN MULTIPLE VALUES

@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.

# Re: IF FUNCTION WITH INDEX AND MATCH TO RETURN MULTIPLE VALUES

@Riny_van_Eekelen This is brilliant! Thanks a million.

# Re: IF FUNCTION WITH INDEX AND MATCH TO RETURN MULTIPLE VALUES

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.