Returning a name from a exported data sheet based off two criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-1510564%22%20slang%3D%22en-US%22%3EReturning%20a%20name%20from%20a%20exported%20data%20sheet%20based%20off%20two%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1510564%22%20slang%3D%22en-US%22%3E%3CP%3EExported%20a%20csv%20of%20data%20to%20extract%20the%20person%20of%20a%20department%20that%20completed%20the%20task%20identified.%20I've%20tried%20multiple%20things%20from%20index%20match%20to%20%22if%20ands%22%20can%20someone%20let%20me%20know%20an%20easy%20way%20to%20reference%20these%20two%20criteria%20and%20display%20a%20name%20in%20the%20scorecard%20cell%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1510564%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1510596%22%20slang%3D%22en-US%22%3ERe%3A%20Returning%20a%20name%20from%20a%20exported%20data%20sheet%20based%20off%20two%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1510596%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F723010%22%20target%3D%22_blank%22%3E%40matperal%3C%2FA%3E%26nbsp%3B%2C%20can%20you%20upload%20some%20sample%20data%20without%20any%20confidential%20information%3F%20It's%20a%20lot%20easier%20to%20answer%20your%20specific%20question%20with%20your%20data%20as%20a%20starting%20point.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1510639%22%20slang%3D%22en-US%22%3ERe%3A%20Returning%20a%20name%20from%20a%20exported%20data%20sheet%20based%20off%20two%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1510639%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383224%22%20target%3D%22_blank%22%3E%40TheAntony%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1510662%22%20slang%3D%22en-US%22%3ERe%3A%20Returning%20a%20name%20from%20a%20exported%20data%20sheet%20based%20off%20two%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1510662%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F723010%22%20target%3D%22_blank%22%3E%40matperal%3C%2FA%3E%26nbsp%3B%2C%20will%20there%20only%20be%20one%20person%20per%20Department%2FForm%20combination%20as%20the%20sample%20data%20suggests%3F%20If%20so%20then%20you%20can%20use%20the%20index%20match%20formulas%20I%20used.%20If%20you%20have%20the%20new%20dynamic%20arrays%2C%20then%20you%20can%20use%20the%20filter%20functions%20as%20well.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1510689%22%20slang%3D%22en-US%22%3ERe%3A%20Returning%20a%20name%20from%20a%20exported%20data%20sheet%20based%20off%20two%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1510689%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383224%22%20target%3D%22_blank%22%3E%40TheAntony%3C%2FA%3E%26nbsp%3Bin%20theory%2C%20yes.%20However%20it%20is%20possible%20that%20two%20people%20from%20the%20same%20department%20input%20more%20than%20one%20form.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1510693%22%20slang%3D%22en-US%22%3ERe%3A%20Returning%20a%20name%20from%20a%20exported%20data%20sheet%20based%20off%20two%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1510693%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F723010%22%20target%3D%22_blank%22%3E%40matperal%3C%2FA%3E%26nbsp%3B%2C%20if%20there%20were%20more%20than%20one%20person%20then%20how%20would%20you%20want%20that%20displayed.%20Currently%2C%20only%20one%20person%20can%20be%20displayed%20the%20way%20your%20dashboard%20is%20set-up.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1510711%22%20slang%3D%22en-US%22%3ERe%3A%20Returning%20a%20name%20from%20a%20exported%20data%20sheet%20based%20off%20two%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1510711%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383224%22%20target%3D%22_blank%22%3E%40TheAntony%3C%2FA%3E%26nbsp%3Bno%20other%20way.%20it%20is%20an%20inefficiency%20to%20perform%20the%20report%20more%20than%20once.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1510714%22%20slang%3D%22en-US%22%3ERe%3A%20Returning%20a%20name%20from%20a%20exported%20data%20sheet%20based%20off%20two%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1510714%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F723010%22%20target%3D%22_blank%22%3E%40matperal%3C%2FA%3E%26nbsp%3Bhowever%20I%20repeated%20that%20formula%20and%20it%20is%20giving%20me%20a%20name%3F%20return.%20I%20am%20no%20familiar%20with%20the%20filters.%20do%20i%20use%20the%20exact%20filter%20you%20did%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1510723%22%20slang%3D%22en-US%22%3ERe%3A%20Returning%20a%20name%20from%20a%20exported%20data%20sheet%20based%20off%20two%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1510723%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383224%22%20target%3D%22_blank%22%3E%40TheAntony%3C%2FA%3E%26nbsp%3B%26nbsp%3B%3D_xlfn._xlws.FILTER('FSI%20LSI'!%24G%243%3A%24G%2423%2C('FSI%20LSI'!%24D%243%3A%24D%2423%3D'Email%20Data'!K%244)*('FSI%20LSI'!%24O%243%3A%24O%2423%3D'Email%20Data'!%24I10))%20heres%20what%20is%20giving%20me%20the%20name%3F%20error.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1510729%22%20slang%3D%22en-US%22%3ERe%3A%20Returning%20a%20name%20from%20a%20exported%20data%20sheet%20based%20off%20two%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1510729%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F723010%22%20target%3D%22_blank%22%3E%40matperal%3C%2FA%3E%26nbsp%3B%2C%20your%20version%20of%20Excel%20doesn't%20have%20the%20new%20functions.%20That's%20fine%2C%20the%20Index%20Match%20solution%20on%20the%20left%20side%20should%20still%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1513486%22%20slang%3D%22en-US%22%3ERe%3A%20Returning%20a%20name%20from%20a%20exported%20data%20sheet%20based%20off%20two%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1513486%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F723010%22%20target%3D%22_blank%22%3E%40matperal%3C%2FA%3E%26nbsp%3B%2C%20try%20this%20version%20with%20a%20helper%20column.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1513615%22%20slang%3D%22en-US%22%3ERe%3A%20Returning%20a%20name%20from%20a%20exported%20data%20sheet%20based%20off%20two%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1513615%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383224%22%20target%3D%22_blank%22%3E%40TheAntony%3C%2FA%3E%26nbsp%3Bthey're%20matching.%20what%20the%20italics%20mean%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Exported a csv of data to extract the person of a department that completed the task identified. I've tried multiple things from index match to "if ands" can someone let me know an easy way to reference these two criteria and display a name in the scorecard cell?

11 Replies
Highlighted

@matperal , can you upload some sample data without any confidential information? It's a lot easier to answer your specific question with your data as a starting point.

Highlighted
Highlighted

@matperal , will there only be one person per Department/Form combination as the sample data suggests? If so then you can use the index match formulas I used. If you have the new dynamic arrays, then you can use the filter functions as well.

Highlighted

@TheAntony in theory, yes. However it is possible that two people from the same department input more than one form.

Highlighted

@matperal , if there were more than one person then how would you want that displayed. Currently, only one person can be displayed the way your dashboard is set-up. 

Highlighted

@TheAntony no other way. it is an inefficiency to perform the report more than once.

Highlighted

@matperal however I repeated that formula and it is giving me a name? return. I am no familiar with the filters. do i use the exact filter you did?

Highlighted

@TheAntony  =_xlfn._xlws.FILTER('FSI LSI'!$G$3:$G$23,('FSI LSI'!$D$3:$D$23='Email Data'!K$4)*('FSI LSI'!$O$3:$O$23='Email Data'!$I10)) heres what is giving me the name? error.

Highlighted

@matperal , your version of Excel doesn't have the new functions. That's fine, the Index Match solution on the left side should still work.

Highlighted

@matperal , try this version with a helper column. 

Highlighted

@TheAntony they're matching. what the italics mean?