First & Last 2 sheet name match.

petersond

New Member
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!

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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
Code:
``=CONCATENATE(A1,B1)``

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

Replies
11
Views
469
Replies
4
Views
142
Replies
5
Views
209
Replies
2
Views
342
Replies
13
Views
689

1,203,242
Messages
6,054,349
Members
444,717
Latest member
melindanegron

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back