I have two different excel sheets. One excel sheet some values which I need to compare some column and if they matches then need to copy that values on another excel sheet. There is lots of rows which cannot be filled manually, so i was thinking of any formula if i can use to copy that.
example :
For example I need to fill orange column (N) in Sheet2 based on the matching combination from Sheet1. If Column C and Column K matches the value in both sheet, then I need to copy the value of Column N from sheet 1 to Sheet 2.
PS : the rows in both sheets are not in same orders.
Adding some excel sample for easier output.
Sheet1
Item_code Item_id Item_name
AB 011 ABC
CD 012 PQR
EE 045 AAS
DF 015 AAF
SD 032 DDF
FG 078 KKJ
TY 013 NJY
Sheet2
Item_code Item_id Item_name
TY 013
SD 032
CD 012
DF 015
AB 011
FG 078
EE 045
CodePudding user response:
If I'm understanding the question correctly, would an IF statement suffice here? =IF(AND($C1=Sheet1!$C1,$K1=Sheet1!$K1),Sheet1!$N1,"")
CodePudding user response:
=IFERROR(INDEX(SHEET1!N:N,MATCH(1,(SHEET1!C:C=@C:C)*(SHEET1!K:K=@K:K),0)),"")
would give you the desired result. But it's better to use table references (but we don't know the table names).