Last Name- First Name problem

[dickpierce]

I have four columns of data. Each column has about 660 names in them.

a: Last Name
b: First Name
c: Last Name
d: First Name

I'm using the following formula in column e:=IF(COUNTIF(c:c,A2)>0,"",A2)

It works great unless there are matching first names in b and d. What is the formula I could use that would work that would weed out same first names?

 

[Norie]

What are you actually trying to do?

 

[dickpierce]

Here's what I'm trying to do: I want to match the last names of people in column a with the last names in column c. If there is no match, the last name of a person from column a should appear in column e. Using the formula I quoted in my previous post works well, however if a person with a first name that is the same in both column b and d but with a different last name, his last name will not appear in column e. I want the name to appear in e. Does this help?

 

[dickpierce]

Here's an addendum to the problem I'm having. These are the results I'm getting using the formula:=IF(COUNTIF(c:c,a44)>0,"",a44)
However, notice the name phil bradley in columns a-b. The result is blank, but should not be.

a b c d e

Bonura Zeke Berger Wally Bonura
Boone Ray Berra Yogi
Boyle Jack Biggio Craig Boyle
Bradley Phil Bishop Max
Brandt Jackie Blair Paul Brandt
Bransfield Kitty BlasingameDon Bransfield
Braun Steve Blauser Jeff Braun
Bresnahan Roger Blue Lu
Bressler Rube Bluege Ossie Bressler
Bressoud Eddie Boggs Wade Bressoud
Bridwell Al Bolling Frank Bridwell
Briggs Johnny Bonds Barry Briggs
Brookens Tom Bonds Bobby Brookens
Brosius Scott Bonilla Bobby Brosius
Brown Ollie Boone Bob Brown
Browne George Boone Bret Browne
Browning Pete Boone Ray Browning
Buechele Steve Bottomley Jim Buechele
Buford Don Boudreau Lou
Buhner Jay Bowa Larry Buhner
Bumbry Al Boyer Clete Bumbry
Burdock Jack Boyer Ken Burdock
Burgess Smoky Bradley Bill

 

[Aladin Akyurek]

Here's an addendum to the problem I'm having. These are the results I'm getting using the formula:=IF(COUNTIF(c:c,a44)>0,"",a44)
However, notice the name phil bradley in columns a-b. The result is blank, but should not be.

a b c d e

Bonura Zeke Berger Wally Bonura
Boone Ray Berra Yogi
Boyle Jack Biggio Craig Boyle
Bradley Phil Bishop Max
Brandt Jackie Blair Paul Brandt
Bransfield Kitty BlasingameDon Bransfield
Braun Steve Blauser Jeff Braun
Bresnahan Roger Blue Lu
Bressler Rube Bluege Ossie Bressler
Bressoud Eddie Boggs Wade Bressoud
Bridwell Al Bolling Frank Bridwell
Briggs Johnny Bonds Barry Briggs
Brookens Tom Bonds Bobby Brookens
Brosius Scott Bonilla Bobby Brosius
Brown Ollie Boone Bob Brown
Browne George Boone Bret Browne
Browning Pete Boone Ray Browning
Buechele Steve Bottomley Jim Buechele
Buford Don Boudreau Lou
Buhner Jay Bowa Larry Buhner
Bumbry Al Boyer Clete Bumbry
Burdock Jack Boyer Ken Burdock
Burgess Smoky Bradley Bill

If A:B is sorted on A and Cand D sorted on C, try:

E1:

=IF(LOOKUP(A1,$C$1:$C$23)=A1,IF(INDEX($D$1:$D$23,MATCH(A1,$C$1:$C$23,1))=B1,"",A1),A1)

 

[dickpierce]

ray:

You are a God!

Works like a charm. Thanks! But out of curiosity, is there another formula? Like my original one?

 

[Aladin Akyurek]

...

[O]ut of curiosity, is there another formula? Like my original one?

I don't think so.

 

[dickpierce]

If you say so then, it must not exist. Thanks again.

 

[dickpierce]

Oh oh! I found a mistake using the given formula.

Hayes Frankie Greenberg Hank Hayes
Hayes Jackie Grich Bobby Hayes
Heath Jeff Griffey Jr. Ken
Heath Mike Griffey Sr. Ken Heath
Heathcote Cliff Grissom Marquis Heathcote
Hegan Jim Groat ****
Held Woodie Groh Heinie
Helms Tommy Grote Jerry Helms
Helton Todd Guerrero Pedro Helton
Hemphill Charlie Guerrero Vladimir Hemphill
Hemsley Rollie Guillen Ozzie
Henderson Dave Gwynn Tony Henderson
Henderson Ken Hack Stan Henderson
Henrich Tommy Hafey Chick
Hernandez Jose Haller Tom Hernandez
Herndon Larry Hamilton Billy Herndon
Hickman Charlie Hamner Granny
Hickman Jim Hansen Ron Hickman
Higginson Bobby Hargrove Mike Higginson
High Andy Harper Tommy High
Hill Glenallen Harrah Toby Hill
Hinton Chuck Harris Bucky Hinton
Hisle Larry Hart Jim Ray
Hoak Don Hartnett Gabby
Hoblitzel **** Hartsel Topsy Hoblitzel
Hofman Solly Hayes Frankie Hofman

Notice the name Frankie Hayes. It should be a blank. If I change your formula to:=IF(LOOKUP(A1,$C$1:$C$23)=A1,IF(INDEX($D$1:$D$23,MATCH(A1,$C$1:$C$23,0))=B1,"",A1),A1). I put a zero at the end of Match, it solves this problem but then other errors occur. See below.

Avila Bobby Bailey Bob
Baerga Carlos Bailey Ed Baerga
Bailey Ed Baines Harold Bailey

Bailey should not appear. Help!

 

[Aladin Akyurek]

Oh oh! I found a mistake using the given formula.

Hayes Frankie Greenberg Hank Hayes
Hayes Jackie Grich Bobby Hayes
Heath Jeff Griffey Jr. Ken
Heath Mike Griffey Sr. Ken Heath
Heathcote Cliff Grissom Marquis Heathcote
Hegan Jim Groat ****
Held Woodie Groh Heinie
Helms Tommy Grote Jerry Helms
Helton Todd Guerrero Pedro Helton
Hemphill Charlie Guerrero Vladimir Hemphill
Hemsley Rollie Guillen Ozzie
Henderson Dave Gwynn Tony Henderson
Henderson Ken Hack Stan Henderson
Henrich Tommy Hafey Chick
Hernandez Jose Haller Tom Hernandez
Herndon Larry Hamilton Billy Herndon
Hickman Charlie Hamner Granny
Hickman Jim Hansen Ron Hickman
Higginson Bobby Hargrove Mike Higginson
High Andy Harper Tommy High
Hill Glenallen Harrah Toby Hill
Hinton Chuck Harris Bucky Hinton
Hisle Larry Hart Jim Ray
Hoak Don Hartnett Gabby
Hoblitzel **** Hartsel Topsy Hoblitzel
Hofman Solly Hayes Frankie Hofman

Notice the name Frankie Hayes. It should be a blank. If I change your formula to:=IF(LOOKUP(A1,$C$1:$C$23)=A1,IF(INDEX($D$1:$D$23,MATCH(A1,$C$1:$C$23,0))=B1,"",A1),A1). I put a zero at the end of Match, it solves this problem but then other errors occur. See below.

Avila Bobby Bailey Bob
Baerga Carlos Bailey Ed Baerga
Bailey Ed Baines Harold Bailey

Bailey should not appear. Help!

You need to adapt/adjust the ranges: $C$1:$C$23 ---> $C$1:$C$26. And you do not need to set the match-type to 0 in MATCH. A:B must be sorted in ascending order, first on A then B. The same sort routine for C:D.