Reference values on other sheet when selecting item in dropdown

Question

I have a Google Spreadsheet with 2 sheets. "Table1" and "Table2". Table 1 looks like this:

Column A = Name Column B = Gender

In table2, I created a dropdown list in cilumn A that gets all the values from Table1, column1, rows 1-100. (Using Data > Validation > Criteria > Items from a list) Now in table2, column B, I want to display the value from table1, column B that is in the same row as the value I selected in the dropdown list.

So, let's say in table1 I have

A       B
John    male
Kate    female
Steve   male

If I select "Steve" in the dropdown list on table2, column A, row 1, I want table 2, column B, row 1 to display "male".

I tried to do it with Index, like I did in Excel, but then I get a value error, saying that the value in the dropdown is not a number.

Any help on this one? Thanks a lot!

Solution

The function that you want to use is VLOOKUP. (Read about it here).

VLOOKUP(search_criterion, array, index, sort_order)

So your cell in the second sheet, you would want to use this.

=VLOOKUP(A2,Sheet1!A1:B9,2,FALSE)
                             ^ Set to TRUE if your data is sorted
                          ^ The column where "male" is stored relative to the array given.
              ^ The array where to lookup the value, and retrieve the result
         ^ The value that you want to lookup.

The following example table might help explain the "array" parameter.

  A      B
 joe    male
 dan    male
 mary female

Your lookup array would be the entire dataset. (Note: The first column of your array is where it searches). You would then provide 2 as the index because that is the column from the array that you want to output.

So VLOOKUP searches through the first column of the array for whatever value you provide. When it finds it, it takes the cell in that row, and in the column you specify, and outputs that.