SOLVED

IF FUNCTION WITH INDEX AND MATCH TO RETURN MULTIPLE VALUES

%3CLINGO-SUB%20id%3D%22lingo-sub-2816222%22%20slang%3D%22en-US%22%3EIF%20FUNCTION%20WITH%20INDEX%20AND%20MATCH%20TO%20RETURN%20MULTIPLE%20VALUES%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2816222%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20team%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20having%20trouble%20with%20the%20INDEX%20AND%20MATCH%20Formula.%20Here's%20what%20I'm%20trying%20to%20achieve.%20On%20the%20attached%20Workbook%2C%20I%20have%20two%20sheets.%20Sheet%20%22Data%22%20has%20all%20the%20information%20I%20want%20the%20formula%20to%20search%20from.%20Sheet%20%22Status%22%20is%20where%20I%20want%20the%20results%20returned%20to.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20search%20for%20the%20email%20address%20in%20Colum%20A%20on%20sheet%20%22Status%22%20and%20return%20the%20training%20title%20from%20Colum%20C%20on%26nbsp%3Bsheet%20%22Data%22%20%26nbsp%3Bfor%20the%20matching%20email%20if%20the%20status%20of%20the%20training%20is%20'In%20progress'%2C%20'Not%20started'%20or%20'Registered'.%20Hope%20I'm%20making%20sense.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20what%20I%20cooked%20up%20incorrectly%3A%26nbsp%3B%3CSPAN%3E%3DIF('Data'!C2%3AC14%3D%22In%20Progress%22)%2CINDEX(Data!D2%3AD12%2CMATCH(%3C%2FSPAN%3E%3CSPAN%3EStatus!A2%3C%2FSPAN%3E%3CSPAN%3E%2CData!B2%3AB12%2C0)%2C2)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EPlease%20help%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2816222%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2816276%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20FUNCTION%20WITH%20INDEX%20AND%20MATCH%20TO%20RETURN%20MULTIPLE%20VALUES%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2816276%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1176658%22%20target%3D%22_blank%22%3E%40lamarsmith%3C%2FA%3E%26nbsp%3BYou%20tagged%20your%20question%20with%20%22Excel%20for%20Web%22.%20Then%20you%20can%20use%20the%20FILTER%20function%20in%20conjunction%20with%20TEXTJOIN%20as%20demonstrated%20in%20the%20attached%20file.%26nbsp%3B%20Note%20that%20I%20converted%20the%20Data%20into%20a%20structured%20table%20(called%20%22tblData%22).%20This%20makes%20referencing%20a%20lot%20easier.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2816627%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20FUNCTION%20WITH%20INDEX%20AND%20MATCH%20TO%20RETURN%20MULTIPLE%20VALUES%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2816627%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BThis%20is%20brilliant!%20Thanks%20a%20million.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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 (New 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