I am trying to cross reference two worksheet and if a name appears on both
worksheets a Y would appear in the box.

Can anyone help??

You will need to give a bit more detail than this on what you want to
achieve. Which "box" do you refer to? How is your data laid out? Which
column(s) would contain the names? etc.

As a general note, you can have a MATCH formula in a column on one
sheet which looks at the other sheet for an exact match, and a similar
formula in the other sheet to look at the first sheet, so against each
name you can return a "Y" if there is a match - is this what you want?

Pete

On Jan 9, 12:56*am, Christie
wrote:
I am trying to cross reference two worksheet and if a name appears on both
worksheets a Y would appear in the box.

Can anyone help??

Sheet1 has the names you want to check against a second sheet. Sheet2
has the other list you want to compare, both sheets have the names in
columnA.

On sheet1, in B2 (where you want the Y to appear, we want to check the
namen in A2 against Sheet2 columnA. Let's set the range on sheet2 to 200
rows. The formula in B2 would be:

=IF(ISERROR(MATCH(A1,Sheet2!$A$1:$A$200,0)),"","Y" )

Now copy that formula down to check the rest of the names on Sheet1


Christie;174798 Wrote:
I am trying to cross reference two worksheet and if a name appears on
both
worksheets a Y would appear in the box.


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=48360

Thank you for your reply.
This achieved what I wanted however now I want to see if both the first name
and surname I have on one sheet matches the names on the other sheet and they
are in seperate cells. How would I do this?

"JBeaucaire" wrote:


Sheet1 has the names you want to check against a second sheet. Sheet2
has the other list you want to compare, both sheets have the names in
columnA.

On sheet1, in B2 (where you want the Y to appear, we want to check the
namen in A2 against Sheet2 columnA. Let's set the range on sheet2 to 200
rows. The formula in B2 would be:

=IF(ISERROR(MATCH(A1,Sheet2!$A$1:$A$200,0)),"","Y" )

Now copy that formula down to check the rest of the names on Sheet1


Christie;174798 Wrote:
I am trying to cross reference two worksheet and if a name appears on
both
worksheets a Y would appear in the box.


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=48360

One way

Assuming first names and surnames are in cols A and B in both Sheet1/2
In Sheet1,
Put in C1, normal ENTER:
=IF(OR(A1="",B1=""),"",IF(ISNUMBER(MATCH(1,INDEX(( Sheet2!A$1:A$100=A1)*(Sheet2!B$1:B$100=B1),),0))," Y",""))
Copy down as far as required. Adapt the ranges to suit.

Pl press the YES buttons (like the ones below) in ALL responses which helped
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---
"Christie" wrote:
now I want to see if both the first name and surname
I have on one sheet matches the names on the other sheet
and they are in seperate cells. How would I do this?

Thank you this helped a great deal however is their a way that I can include
in the formula that if one sheet has a christian name, eg Anna and the other
sheet has both christian and middle name, eg Anna Rose that this will show as
a match? Also to include this in matching address's???

Christie

"Max" wrote:

One way

Assuming first names and surnames are in cols A and B in both Sheet1/2
In Sheet1,
Put in C1, normal ENTER:
=IF(OR(A1="",B1=""),"",IF(ISNUMBER(MATCH(1,INDEX(( Sheet2!A$1:A$100=A1)*(Sheet2!B$1:B$100=B1),),0))," Y",""))
Copy down as far as required. Adapt the ranges to suit.

Pl press the YES buttons (like the ones below) in ALL responses which helped
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---
"Christie" wrote:
now I want to see if both the first name and surname
I have on one sheet matches the names on the other sheet
and they are in seperate cells. How would I do this?

It becomes decidedly more difficult. A measure of success might be
achieveable using fuzzier search terms within the criteria eg: ...
ISNUMBER(SEARCH(...))

Lets say you have in Sheet2's A1:B1,
data such as: Anna Rose, Mary
(Mary in B1)

And in Sheet1,
you have the lookups
in A1: Anna, in B1: Mary

This revision of the earlier, placed in C1, normal ENTER:
=IF(OR(A1="",B1=""),"",IF(ISNUMBER(MATCH(1,INDEX(I SNUMBER(SEARCH(TRIM(A1)&"
",Sheet2!A$1:A$100))*(Sheet2!B$1:B$100=B1),),0))," Y",""))
would return a correct: "Y" for the data in Sheet2. It will avoid returning
a spurious "Y" should you have: Annabel, Mary in Sheet2 (instead of: Anna
Rose, Mary).

Above is still not watertight of course, but the method might help you to
narrow down the desired searches.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---
"Christie" wrote:
Thank you this helped a great deal however is their a way that I can include
in the formula that if one sheet has a christian name, eg Anna and the other
sheet has both christian and middle name, eg Anna Rose that this will show as
a match? Also to include this in matching address's???