CODEWITHSUNDEEP
arraysgoogle-sheetsgoogle-sheets-formulamatchvlookup
JohnnyMelou
JohnnyMelou

Reputation: 31

Google Sheets: Trying to get values for Data in a column and row

Not sure I was able to explain myself too well in the title so here it goes:

I have been trying to figure this one out using the Filter function, but I am unable to make it work, and I am sure there must be an easy way to do it. The idea is that I have a column with the names of the members of a team, and each team member needs to perform a number of actions in a set of tasks. I am counting the amount of actions performed by each team member on each task, and trying to filter those by team member and by task.

So it goes like this: I have a list of names in column B. The number of actions in column C and the task would be selected from a dropdown menu done with 'Data validation'. When I select the task from my drop down menu, the number of actions would change to reflect the actions performed per agent on the selected task.

I added an example too. In the example I filtered the data in sheet 'Data' by names, but I don't know how to add a criteria that would also filter by the tasks on row 1 in the sheet 'Data', or if it is even possible.

Example: https://docs.google.com/spreadsheets/d/1PcdwNHDagfSmtF2Hl27YnrsAsPsF3A5OLuo2YcqGFdk/edit#gid=0

Thanks!

Upvotes: 1

Views: 82

Answers (1)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(IFNA(VLOOKUP(B2:B&C1, 
 SPLIT(FLATTEN(Data!A2:A&Data!B1:E1&"×"&Data!B2:E), "×"), 2, 0)))

enter image description here

Upvotes: 1

Related Questions