excel - Find value from one cell, copy value from cell near to that cell and paste it to another sheet -
i have 2 sheets different values. need find value 1 cell.sheet1 in sheet2 , copy value nextcell_in_the_same_row.sheet1 nextcell_in_the_same_row.sheet2. difficult explain let @ example bellow.
e.g. before
first sheet: b 1 aaa 123 2 bbb 456 3 ccc 789 4 ddd 122 second sheet: b 1 aaa 2 ada 3 cca 4 ccc
after
first sheet: b 1 aaa 123 2 bbb 456 3 ccc 789 4 ddd 122 second sheet: b 1 aaa *need find value in first sheet , copy value b2 because aaa in a1* 2 ada *value not exist in first sheet copy nothing* 3 cca *not need copy because no value in first sheet* 4 ccc *need copy value b3*
thank much!
use vlookup
along iferror
.
=iferror(vlookup(a1, sheet1!a:b, 2, 0), "")
this described (well described, way!) in question. drag formula down in sheet2
till bottom.
vlookup
takes value of a1
in sheet 2 (no sheet reference because value in same sheet formula) , looks in column of sheet1
.
it returns second value (hence why 2
) of table selected in formula (column 1, column b 2).
the 0 tells vlookup
exact matches. don't need approximate match here.
and ifferror
there in case vlookup
doesn't find (like ada
) , instead of giving #n/a
, returns empty cell, ""
.
Comments
Post a Comment