Match 2 columns in different sheets and copy 3rd column from one sheet into another for matched cell-CodePudding

I have the following data in 2 excel sheets in same file:

Sheet 1:

COLUM1
Field1
Field2
Field3
Field4
Field5
Field6

Sheet2:

COLUM1  COLUM2
Field1  Val1
Field2  Val2
Field3  Val2
Field4  Val3
Field5  Val2
Field6  Val5

What I want is an excel native method to match the values in COLUM1 of both sheets and add the corresponding COLUM2 value in a new column in Sheet 1. I looked for IFMATCH and VLOOKUP but couldn't figure out a way to copy the data as I am very inexperienced with excel.

CodePudding user response：

Standard sort routine.

row a = 1
row b = 1
loop until value in sheet1 column A row a <> ""
   loop until value in sheet2 column A row b <> ""
      if test for equal values in each Col Row then
          sheet1 column B row a = sheet2 column B row b
      end if
      row b = row b   1         
   next
   row a = row a   1
   row b = 1
next

This will compare each sheet1 column A row with every row in sheet2 column A if any match it will make sheet1 column B row a = sheet2 column B row b

Were you looking for a macro? or are you trying to do this on the fly every time you change data in any particular cell?

If the later is the case then

=VLOOKUP(A1,Sheet2!$A$1:$B$7,2,FALSE)

A1 is the value you are looking for. $A$1:$B$7 is the area you are comparing you'll have to know the area you are pulling the data from top left to bottom right 2 is the column to return the data from in this case #2 is column B and false is blank if nothing is matches

this will adapt as you change the values in Sheet1 to what you are searching for. If you change the values in Column B on sheet2 it will dynamically change sheet1 accordingly

**** sorry my previous vlookup was wrong. **** It's right now ****

But what if !!! the value in Sheet1 columnA doesn't correspond to any values in the area sheet2 $A$1:$B$7???

=IF(ISNA(VLOOKUP(A1,Sheet2!$A$1:$B$7,2,FALSE)=TRUE),"",VLOOKUP(A1,Sheet2!$A$1:$B$7,2,FALSE))

This is the Conditional statement you want. Insert in and copy downward accordingly

CodePudding user response：

In newer versions of Excel, use