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.
Comments
Post a Comment