In excel workbook I am trying to copy data from sheet1 to sheet2 with a condition, IF((Sheet1!B2:B80=Sheet2!A2:A80),Sheet2!D2) i am not getting the result.please help me solve this.
在excel工作簿中我试图将数据从sheet1复制到sheet2,条件为IF((Sheet1!B2:B80 = Sheet2!A2:A80),Sheet2!D2)我没有得到结果。请帮我解决这个问题。
I am trying to copy from sheet1 to sheet2 with a condition if the the value in sheet 1,column B is equal to the value in sheet2 column A the value in column D of sheet 2 should be copied to column G of sheet1
我试图从sheet1复制到sheet2,条件是如果工作表1,B列中的值等于sheet2列A中的值,则应将工作表2的D列中的值复制到sheet1的G列
4 个解决方案
#1
Try do debug it step by step. What happens, if you change your formula in:
尝试一步一步地调试它。如果您更改公式,会发生什么:
=IF((Sheet1!B2:B3=Sheet2!A2:A3),Sheet2!D2)
?
Or
=IF((Sheet1!B2:B3=Sheet2!A2:A3),"the same values")
Maybe not all the values are really exactly the same in columns A and B. Or D2 doesn't contain what you expect.
也许并非所有值在A列和B列中都完全相同。或者D2不包含您期望的值。
#2
Put the following formula in the first cell of column G
将以下公式放在G列的第一个单元格中
=IF((Sheet1!B1=Sheet2!A1), Sheet2!D1)
then copy this cell down as far as you need to. Excel will change all the cell references for you.
然后根据需要将此单元格复制下来。 Excel将为您更改所有单元格引用。
#3
this should do what you are after.
这应该做你想要的。
=IF(Sheet1!B1=Sheet2!A1,Sheet2!D1)
#4
=IF(EXACT(Sheet1!B1,Sheet2!A1),Sheet2!D1)
#1
Try do debug it step by step. What happens, if you change your formula in:
尝试一步一步地调试它。如果您更改公式,会发生什么:
=IF((Sheet1!B2:B3=Sheet2!A2:A3),Sheet2!D2)
?
Or
=IF((Sheet1!B2:B3=Sheet2!A2:A3),"the same values")
Maybe not all the values are really exactly the same in columns A and B. Or D2 doesn't contain what you expect.
也许并非所有值在A列和B列中都完全相同。或者D2不包含您期望的值。
#2
Put the following formula in the first cell of column G
将以下公式放在G列的第一个单元格中
=IF((Sheet1!B1=Sheet2!A1), Sheet2!D1)
then copy this cell down as far as you need to. Excel will change all the cell references for you.
然后根据需要将此单元格复制下来。 Excel将为您更改所有单元格引用。
#3
this should do what you are after.
这应该做你想要的。
=IF(Sheet1!B1=Sheet2!A1,Sheet2!D1)
#4
=IF(EXACT(Sheet1!B1,Sheet2!A1),Sheet2!D1)