Automatically fill data from another sheet

问题:

Main Question

I would like to auto-fill Sheet A with values from Sheet B in Excel 2013. My data are in two sheets in the same workbook.

Example

=========== Sheet 1 ===========     =========== Sheet 2 ===========

location   year   val1   val2       location   year   val1   val2
USA.VT     1999                     USA.VT     1999     6      3
USA.VT     2000                     USA.VT     2000     3      2
USA.VT     2001                     USA.VT     2001     4      1
USA.VT     2002                     USA.VT     2002     9      5
USA.NH     1999                     USA.NH     1999     3      6
USA.NH     2000                     USA.NH     2002     12     56
USA.NH     2001                     USA.ME     1999     3      16
USA.NH     2002                     USA.ME     2002     4      5
USA.ME     1999
USA.ME     2000
USA.ME     2001
USA.ME     2002

I would like to use some function or formula to automatically populate Sheet 1 based on the values in Sheet 2 according to: location, year, and the column (val1 or val2). Non-matches would be zero-filled.

This would result in the following:

=========== Sheet 1 ===========

location   year   val1   val2
USA.VT     1999    6      3
USA.VT     2000    3      2
USA.VT     2001    4      1
USA.VT     2002    9      5 
USA.NH     1999    3      6
USA.NH     2000    0      0
USA.NH     2001    0      0
USA.NH     2002    12     56
USA.ME     1999    3      16
USA.ME     2000    0      0
USA.ME     2001    0      0
USA.ME     2002    4      5

I have tried VLOOKUP, INDEX, and MATCH, but I'm having no luck.

Any help would be greatly appreciated!

回答1:

Put this Array formula in C2:

=IFERROR(INDEX(Sheet2!C$2:C$9,MATCH($A2&$B2,Sheet2!$A$2:$A$9&Sheet2!$B$2:$B$9,0)),0)

Being an array formula you must confirm with Ctrl-Shift-Enter to exit the edit mode instead of Enter.

Then copy over one column and down.

The picture is not exact because I left it on one sheet.