excel - Automatically fill data from another sheet -


main question

i auto-fill sheet a values sheet b in excel 2013. data in 2 sheets in 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 use function or formula automatically populate sheet 1 based on values in sheet 2 according to: location, year, , column (val1 or val2). non-matches zero-filled.

this result in 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, , match, i'm having no luck.

any appreciated!

put 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 array formula must confirm ctrl-shift-enter exit edit mode instead of enter.

then copy on 1 column , down.

the picture not exact because left on 1 sheet.

![enter image description here


Comments

Popular posts from this blog

sublimetext3 - what keyboard shortcut is to comment/uncomment for this script tag in sublime -

java - No use of nillable="0" in SOAP Webservice -

ubuntu - Laravel 5.2 quickstart guide gives Not Found Error -