How to compare two columns in Excel from different sheets and copy values from a corresponding column if the first two columns match

Question

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

User · Answer

Make a truth table and use SUMPRODUCT to get the values. Copy this into cell B1 on Sheet2 and copy down as far as you need:
=SUMPRODUCT(--($A1 = Sheet1!$A:$A), Sheet1!$B:$B)
the part that creates the truth table is:
--($A1 = Sheet1!$A:$A)
This returns an array of 0's and 1's. 1 when the values match and a 0 when they don't. Then the comma after that will basically do what I call "funny" matrix multiplication and will return the result. I may have misunderstood your question though, are there duplicate values in Column A of Sheet1?

User · Answer

As kmcamara discovered  this is exactly the kind of problem that VLOOKUP is intended to solve  and using vlookup is arguably the simplest of the alternative ways to get the job done   In addition to the three parameters for lookup value  table range to be searched  and the  column index for return values  VLOOKUP takes an optional fourth argument that the Excel documentation calls the  range lookup     Expanding on deathApril s explanation  if this argument is set to TRUE  or 1  or omitted  the table range must be  sorted in ascending order of the values in the first column of the range for the function to return what would typically be understood to be the  correct  value  Under this default behavior  the function will return a value based upon an exact match  if one is found  or an approximate match if an exact match is not found    If the match is approximate  the value that is returned by the function will be based on the next largest value that is less than the lookup value  For example  if  12AT8003  were missing from the table in Sheet 1  the lookup formulas for that value in Sheet 2 would return  2   since  12AT8002  is the largest value in the lookup column of the table range that is less than  12AT8003    VLOOKUP s default behavior makes perfect sense if  for example  the goal is to look up rates in a tax table    However  if the fourth argument is set to FALSE  or 0   VLOOKUP returns a looked-up value only if there is an exact match  and an error value of  N A if there is not  It is now the usual practice to wrap an exact VLOOKUP in an IFERROR function in order to catch the no-match gracefully  Prior to the introduction of IFERROR  no matches were checked with an IF function using the VLOOKUP formula once to check whether there was a match  and once to return the actual match value   Though initially harder to master  deusxmach1na s proposed solution is a variation on a powerful set of alternatives to VLOOKUP that can be used to return values for a column or list to the left of the lookup column  expanded to handle cases where an exact match on more than one criterion is needed  or modified to incorporate OR as well as AND match conditions among multiple criteria   Repeating kcamara s chosen solution  the VLOOKUP formula for this problem would be       VLOOKUP A1 Sheet1 A 1 B 600 2 FALSE

User · Answer

Try   sheet 2 a1  vlookup sheet2a1 sheet1 a 1  b 6 2    Then drag it down   It should work

User · Answer

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

[excel] How to compare two columns in Excel (from different sheets) and copy values from a corresponding column if the first two columns match?

Examples related to excel

Examples related to excel-formula