CODEWITHSUNDEEP
excel
fammi
fammi

Reputation: 215

Find values from one excel sheet in another

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.

thanks

Upvotes: 2

Views: 69155

Answers (3)

deusxmach1na
deusxmach1na

Reputation: 368

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))

Upvotes: 1

assylias
assylias

Reputation: 328568

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:

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

Upvotes: 6

Marc
Marc

Reputation: 11613

Use the approach described here: 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.:

=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.

Upvotes: 1

Related Questions