Compare cells in two sheets

[geobruin]

Hello.

I have two sheets (sheet1 and sheet2). I have data values in column A of both sheets. I need to look through the values in column A of sheet 1 and return a "y" in column B of sheet 2 if the value in column A of sheet 2 can be found. I have tried to illustrate this below:

ABAB

1 1y
2 2y
3 3y
4 5
6 6y

I suppose I will be using "countif" but I don't know how to specify "if exists in column A of sheet 1" as the condition.

Thank you in advance.

 

[Marcelo Branco]

Hi,

Try this in B1 of Sheet2
=IF(COUNTIF(Sheet1!$A$1:$A$200,A1), "Y","")
copy down

You can increase the range $A$1:$A$200 if necessary

HTH

M.

 

[Trebor76]

Hi there,

Try the following formula, just change the ranges to suit:

=IF(ISERROR(VLOOKUP(A2,Sheet1!A2:A30,1,FALSE)),A2,VLOOKUP(A2,Sheet1!A2:A30,1,FALSE)&"y")

HTH

Robert

 

[geobruin]

Fantastic!

Thank you.