Google spreadsheets or Excel, cell matches item from seprate sheet's column

Question

Sheet 1:

Value, Populate me
Value 1, blank_1
Value 2, blank_2

Sheet 2:

Populate X, Populate Y
Value 1, Y1
X2, Value 2
X3, Y3

How do I tell 'Sheet 1'!blank_1 to automatically equal "Populate X" from Sheet2 since it matches one of the values in "Populate X"'s column. Similarly, 'Sheet 1'!blank_2 should automatically equal "Populate Y".

Update:

I have created a public Google Spreadsheet to better illustrate the problem: https://docs.google.com/spreadsheet/ccc?key=0AhvDIvanE182dDFwU1VseHhaT2JIT0FoYXNQOVFwUWc&usp=sharing

In Google Sheets, the comma delimiter in functions will not work in locales that use a comma for a decimal separator. However, the semi-colon delimiter will work in all locales. It is really the discretion of the user, but it is generally recommended to use the semi-colon delimiter in Sheets due to it being universally supported. — AdamL

Qiu Qiu · Accepted Answer · 2013-07-19T18:52:29

In Google spreadsheets you can try something like this:

=IFERROR(IF(match(A2;Data!$A$2:$A$5;0)>0;Data!$A$1;"");IFERROR(IF(match(A2;Data!$B$2:$B$5;0)>0;Data!$B$1;0);""))

Google spreadsheets or Excel, cell matches item from seprate sheet's column

2 Answers