Google Sheets Data – How to Get Data from Another Cell if Cell Matches List

google sheetsgoogle-apps-script

I have a spreadsheet that gathers data from a Google Form. In this form, we ask the users to choose between the different seating plans.

This is an example of the data gathered in the Response sheet:

        column A               
row 1 | Workshop
row 2 | Conference
row 3 | Story time
row 4 | Conference
row 5 | Workshop

In column B of the Response sheet, I want the image of the plan to appear depending on the data from column A. For example, the Workshop seating plan would be "Plan A" and the Conference seating plan would be "Plan B" and so on. I have the name of the plans and the images of the plans in another sheet called "Validation".

The sheet Validation is like this:

         Column A     Column B           
row 1 | Workshop   |   Plan A
row 2 | Conference |   Plan B
row 3 | Story time |   Plan C

Is there a formula that would allow me to accomplish this? Or maybe a script?

EDIT: Here is an example sheet of what I want to accomplish:

Response sheet should look like this:

Validation sheet should look like this:

Here is a link of a test sheet (without a working formula): https://docs.google.com/spreadsheets/d/1QM9m0ue0Isgw1m1a3ww470GF_5Pvxd3P5n-pNdJFOKY/edit?usp=sharing

Best Answer

There is no need a script. Try the next formula

=ARRAYFORMULA(IFERROR(VLOOKUP(A:A,Validation!A:B,2,),""))

Related Solutions

Google Sheets – Validation Based on Multiple Sheets

The best way I can think of to accomplish this is with importRange function.

Here's the general idea. You have three spreadsheet documents. Doc1, Doc2, Doc3.

Doc1: Range A1:A20 contains list of items you want to validate against
Doc2: Range B1:B20 contains list of items you want to also validate against
Doc3: Range C1:C20 contains range you wish to have validated against above lists

Note: I made the ranges distinct just for clarity - you can use A1:AXX or whatever you want for any of these ranges.

In Doc3, use the following formula to import the ranges from Doc1 A1:A20 and Doc2 B1:B20 using the following formula:

Place the following formula in Doc3 (let's say you put it in F1)

=importRange("[key= value on url for Doc1", "A1:A20")

Place the following formula in Doc2 (let's say you put it in G1)

=importRange("[key= value on url for Doc1", "B1:B20")

Now highlight range C1:C20 on Doc3. Right click and choose data validation.

Select for "criteria" as "items from a list"

Choose option "Create list from range"

For the range itself either enter Sheet1!F1:G20, or select it with the mouse.

Your range in Doc3 C1:C20 will now be validated against all the data elements from Doc1 and Doc2. If your list will be growing in size over time, make sure you make your range values large enough to accommodate that.

Google-sheets – copy row from one Google Spreadsheet to another based on certain criteria

I had a similar problem. sheetSpider does not appear to be available anymore. I tried writing my own script and it kinda worked but it was slow and didn't update reliably when the source data changed.

I eventually solved my problem with a pivot table (Data->Pivot table report...). Just select the range, then add all the columns you want to see as Rows (sounds counterintuitive I know) and set Filters for Column B > 1 and Column D = teacher's name. Create a new pivot table for each teacher on its own sheet where the second filter varies. More details about pivot tables in Google Sheets here. For me it is quick and updates well whenever the source data changes.

Related Topic