How to compare two columns in Excel (from different sheets) and copy values from a corresponding column if the first two columns match?
excel, excel-formula
Solution
`Vlookup` is good if the reference values (column A, sheet 1) are in ascending order. Another option is Index and Match, which can be used no matter the order (As long as the values in column a, sheet 1 are unique)
This is what you would put in column B on sheet 2
=INDEX(Sheet1!A$1:B$6,MATCH(A1,Sheet1!A$1:A$6),2)
Setting `Sheet1!A$1:B$6` and `Sheet1!A$1:A$6` as named ranges makes it a little more user friendly.
Problem
I'm trying to compare column a in Sheet 1 with column a in Sheet 2, then copy corresponding values in column b from sheet 1 to column b in Sheet 2 (where the column a values match). I've been trying to read up on how to do this, but I'm not sure if I should be trying to create a macro, or if there's a simpler way to do this, maybe VLOOKUP or MATCH? I'm really not familiar with how these functions work though. Also, if it makes a difference, there will be repeated values in column b of Sheet 2. Sheet 1 ``` 12AT8001 1 12AT8002 2 12AT8003 3 12AT8004 4 12AT8005 5 12AT8006 6 ``` Sheet 2 ``` 12AT8001 12AT8001 12AT8001 12AT8001 12AT8001 12AT8002 12AT8002 12AT8002 12AT8002 12AT8002 12AT8003 12AT8003 12AT8003 12AT8003 12AT8003 ```