Imagine having the following tabs. I want to summarize (let's say avg) in a second tab per each key (room name in the example).

How can I get only values matching by key? VLOOKUP finds only a single entry.

CodePudding user response：

try:

=QUERY({A:B}; 
 "select Col1,avg(Col2) 
  where Col2 is not null 
  group by Col1 
  label avg(Col2)''")

update:

=QUERY({A:B};   
 "select Col1,avg(Col2)    
  where Col1 ='"&'2nd tab'!B1&"'    
  group by Col1    
  label avg(Col2)''")