Excel Match() On Multiple Columns

Question

I have two Excel Worksheets, like worksheet1...

FirstName    LastName
BLEVINS    BARBARA
BLEVINS    CAROLYN
BLEVINS    CAROLYN
BLEVINS    EMILY
BLEVINS    JEANETTE
BLEVINS    OLINKA
BLEVINS    OLINKA
BLEVINS    REBEKAH
BLEVINS    REBEKAH
BLEVINS    SHERI
BLEVINS    TONY
BLEVINS    TONY
BLEVINS    TONY

and worksheet2...

FirstName    LastName
Blevins    Amy
Blevins    Ann
Blevins    Ben
Blevins    Bruce
Blevins    Bruce
Blevins    Christine
Blevins    Danny
Blevins    Dennis
Blevins    Dwayne
Blevins    Fay
Blevins    James
Blevins    Jeff
Blevins    Jim
Blevins    Joe
Blevins    John
Blevins    Johnnie
Blevins    Larry
Blevins    Leanne
Blevins    Mary
Blevins    Michael
Blevins    Patricia
Blevins    Ralph
Blevins    Rebekah
Blevins    Rickey
Blevins    Sandy
Blevins    Stephen
Blevins    T.
Blevins    T.J. and Junie
Blevins    Tony
Blevins    Virginia
Blevins    W.M. and A.D.
Blevins    William
Blevins    William

I've had success matching a single cell's contents to another worksheet, but I'm needing to match multiple cells to another worksheet.

To match just the first name, I would use...

=ISERROR(MATCH(A2,worksheet2!A:A,0))

What I'm wanting to do is see if FirstName AND LastName, in one worksheet, match to FirstName AND Lastname, in the other (I'm trying to find NEW records in worksheet1).

I tried...

=AND(MATCH(A2,worksheet2!A:A,0), MATCH(B2,worksheet2!B:B,0))

Unfortunately, the results were...

TRUE    BLEVINS BARBARA
TRUE    BLEVINS CAROLYN
TRUE    BLEVINS CAROLYN
TRUE    BLEVINS EMILY
TRUE    BLEVINS JEANETTE
#N/A    BLEVINS OLINKA
#N/A    BLEVINS OLINKA
TRUE    BLEVINS REBEKAH
TRUE    BLEVINS REBEKAH
TRUE    BLEVINS SHERI
TRUE    BLEVINS TONY
TRUE    BLEVINS TONY
TRUE    BLEVINS TONY

Obviously, that's completely wrong.

The only TRUE results should be for Rebekah and Tony.

Any ideas as to what I'm doing, incorrectly?

@zx8754 Because I didn't know about `vlookup`, obviously. Hehehe... I'm looking at it, now. — doubleJ, Mar 12 '15 at 21:57

score 1 · Accepted Answer · answered Mar 12 '15 at 22:15

1

Concatenate First and Last name, then use VLOOKUP.

C2

=IF(ISERROR(VLOOKUP(A2&"_"&B2,E:E,1,FALSE)),"No Match","Match")

E2

=F2&"_"&G2

enter image description here

answered Mar 12 '15 at 22:15

zx8754

52,746
12
114
209

1

I referenced another worksheet instead of using different columns in the same worksheet (`=IF(ISERROR(VLOOKUP(C6&"_"&B6,worksheet2!M:M,1,FALSE)),"No Match","Match")`), but this seems to be working correctly. – doubleJ Mar 16 '15 at 20:31
This looks very similar to `MATCH()`. Is there some reason why `VLOOKUP()` worked, but `MATCH()` didn't? – doubleJ Mar 16 '15 at 20:36

score 0 · Answer 2 · answered Mar 12 '15 at 22:19

0

I would recommend a COUNTIFS formula

=if(COUNTIFS(A2,worksheet2!A:A,B2,worksheet2!B:B)>0,"Match","No match")

answered Mar 12 '15 at 22:19

Dan Donoghue

6,056
2
18
36

I tried this, but it gave me `No match` on a record that was in both worksheets. I don't know enough about `COUNTIFS()`, to judge, though. – doubleJ Mar 16 '15 at 20:34

Excel Match() On Multiple Columns

2 Answers2