Find name in one column, return value from another

Question

I have a spreadsheet with responses from a Google Form. They sign up for one of our trivia games, then I make a spreadsheet for each game. I wrote a FILTER function in the corresponding Google Sheet that gives me a list of all the players' names.

What I need is a function to reference the team name from the Responses sheet and give me the information in my filtered game sheet. I would like a function rather than a Javascript script to run manually. I am certain this can be done but the function methodology is escaping me.

As an example, I have created a spreadsheet in Google Sheets with some data. Can someone help me write a formula to put team names in "The Office" tab? Thanks in advance.

https://docs.google.com/spreadsheets/d/1F2bgvHXqA2wNUa98rfESWTeK22va1RMQUjnk6ma2AeA/edit?usp=sharing

Okay everybody, thanks for all your responses. I also need a FILTER function because one person might sign up for multiple games. (I use the same Form for people to register for multiple themed trivia games.) I just added a few people to the Form Responses tab and broke the formula. Anyone want to give it another try? — Dan Burgess
Hello @DanBurgess, what do you mean by "one person might sign up for multiple games"? How about using UNIQUE? Moreover, what is not working in your formula when you add the FILTER function? — ale13

JPV JPV · Accepted Answer · 2020-07-01T17:49:05

In B2 try this formula

=ArrayFormula(if(len(A2:A), vlookup( match("*"&A2:A&"*", 'Form Responses'!C:C, 0), {row('Form Responses'!A:A), 'Form Responses'!B:B}, 2, 0),))

and see if that helps?

Find name in one column, return value from another

3 Answers