Jul 08 2020 07:44 PM
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?
Jul 08 2020 08:32 PM
@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.
Jul 08 2020 09:17 PM
Jul 08 2020 09:43 PM
@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.
Jul 08 2020 09:55 PM
@TheAntony in theory, yes. However it is possible that two people from the same department input more than one form.
Jul 08 2020 10:04 PM
@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.
Jul 08 2020 10:12 PM
@TheAntony no other way. it is an inefficiency to perform the report more than once.
Jul 08 2020 10:13 PM
@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?
Jul 08 2020 10:20 PM
@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.
Jul 08 2020 10:22 PM
@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.
Jul 09 2020 06:32 PM
@matperal , try this version with a helper column.
Jul 09 2020 09:52 PM
@TheAntony they're matching. what the italics mean?