从另一个Excel工作表中查找值

I have a column with the values in A excel sheet 1 1 1 2 2 2 3 3 3 4 4 4.... and i have in B excel sheet another column with values 1 2 4 ...., what i want is read values from B and see if they are in A sheet, for example if value 2 is in B sheet, then write true in a new column in sheet A in front of 2, and similarly false or nothing in front of value 3.

我有一个列在A excel表1 1 1 2 2 2 3 3 3 4 4 4 ....和我有B excel表另一列值1 2 4 ....我想要的是读取B中的值并查看它们是否在A工作表中,例如,如果值2在B工作表中,则在2的前面的工作表A中的新列中写入true,并且在值3前面同样为false或没有任何内容。

thanks

3 个解决方案

#1

You can use a simple VLOOKUP - For example, assuming that the content of cell A1 of sheet B is 2, and that the sheet you call A is called SheetA, you can put the following formula in cell B1:

您可以使用简单的VLOOKUP - 例如,假设工作表B的单元格A1的内容为2,并且您调用A的工作表称为SheetA,则可以将以下公式放在单元格B1中:

 =IF(ISERROR(VLOOKUP(A1,SheetA!A:A,1,FALSE)),"",VLOOKUP(A1,SheetA!A:A,1,FALSE))

#2

Use the approach described here: http://spreadsheetpage.com/index.php/tip/comparing_two_lists_with_conditional_formatting/

使用此处描述的方法:http://spreadsheetpage.com/index.php/tip/comparing_two_lists_with_conditional_formatting/

Key formula is this: =COUNTIF(OldList,D2)=0, which you can use within the conditional formatting context as described, or to generate your true/false indicators as you mention in your question, i.e.:

关键公式是:= COUNTIF(OldList,D2)= 0,您可以在条件格式化上下文中使用,或者如您在问题中提到的那样生成您的真/假指标,即:

=IF(COUNTIF(OldList,D2)=0,FALSE,TRUE)

OldList is just a range, and you don't need to use a named range. But if you don't name the range, just be sure to use absolute references for the range you're searching against.

OldList只是一个范围,您不需要使用命名范围。但是,如果您没有命名范围,请确保对您要搜索的范围使用绝对引用。

#3

Do you want a cool formula you can use to count the number of each matching value. Try this for your original post:

你想要一个很酷的公式,你可以用来计算每个匹配值的数量。试试这个原始帖子:

=IF(SUMPRODUCT(--($A1 =Sheet1!$A:$A) > 0), "True", "False")

And this to count the values:
=SUMPRODUCT(--($A1 =Sheet1!$A:$A))

这要计算值:= SUMPRODUCT( - ($ A1 = Sheet1!$ A:$ A))

#1