First & Last 2 sheet name match.

petersond

New Member
Joined
Aug 5, 2007
Messages
13
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!
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

unnilennium78

New Member
Joined
Aug 13, 2007
Messages
40
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 ????
 

Watch MrExcel Video

Forum statistics

Threads
1,123,052
Messages
5,599,511
Members
414,315
Latest member
Yolanda5050

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top