First & Last 2 sheet name match.

[petersond]

hello everybody.

I have two sheets, first one contains first names in A1:A5, Last names in B1:B5
Sheet two has First names A1:A10, Last names B1:B10

Is there any possible way to have somesort of match function that matches BOTH first & last names from sheet one against sheet two?

currently I have on sheet one, in cell C1..conditional formatting with this formula

=IF(+A1=0," ",IF(ISERROR(MATCH(A1,SPL!A1:A10,0)),"OK","*"))

and then In D1 I have

=IF(+B1=0," ",IF(ISERROR(MATCH(B1,SHEETONE!B1:A10,0)),"OK","*"))

Which matches the very first name in the list against sheet two names... which changes my cell color if there is a match.
These formulas are used throughout Sheet one... and work great... Problem is...

Lets say On sheet one, one name is John Doe

on sheet two. two names are John Smith, and Jane Doe... I get a match in both conditional cells.

I need a way for those cells which with the formulas to only Trigger when I have a FIRST and LAST name match on ONE NAME... not mixing and matching.

Any thoughts or suggestions would be helpful

Thank you!

 

[unnilennium78]

not really sure what u meant but .... why not join the First & Last name together in a column.... for example you use :-


at column C1 u put this formula

=CONCATENATE(A1,B1)

then
at then use Vlookup to compare 2 sheets ????