Excel 2007 : Match first and surname

**sykot** · 08-16-2009, 08:18 AM

Hello,
After searching a while and doing some test i couldnt get out of my problem.
My Question
When i fill in sheet 2 the name and surname i want that in member cell a answer as yes or no. so if the name and surname is the same as in sheet 1 there need to be yes if the name is not in sheet 1 then a answer as no

I tried a lot of formula such as exact, if, vlookup, index

Can someone fix this problem and give me the correct formula ?

Sorry for bad english.
Thank you

here the file.
http://svanatolia.nl/sample.xlsx

**martindwilson** · 08-16-2009, 08:24 AM

1.what version excel have you got? your profile says 2003 this is the 2007 forum.
2, are first names /surnames in two different cells on both sheets?

**sykot** · 08-16-2009, 08:28 AM

Yes
The first and surname is both in apart cell.
I am using office 2008 on mac.

if you can help me please

**arthurbr** · 08-16-2009, 08:31 AM

Hi, please note that everyone here is volunteering on his/her spare time. If you need urgent help I can move your thread to paying services which will help you on demand.

**martindwilson** · 08-16-2009, 08:33 AM

assuming lists in col a/b on both sheets in sheet 2
=IF(ISNUMBER(MATCH(A1&B1,INDEX(Sheet1!$A$1:$A$100&Sheet1!$B$1:$B$100,0),0)),"yes","no") adjust ranges to suit

**sykot** · 08-16-2009, 08:41 AM

I tried but i get an error
\1

**martindwilson** · 08-16-2009, 08:57 AM

well this is working on my 2007 see if it works in your 2008 version
if not try this array entered formula
=IF(ISNUMBER(MATCH(1,(A1=Sheet1!A1:A100) *(B1=Sheet1!B1:B100),0)),"yes","no") confirmed with ctrl+shif+enter
see how to enter array formula
in my signature below