# compare/match multiple column values in multiple excel sheet



## ks1 (Sep 17, 2008)

i am uploading excel work book with 4 sheets, sheet no1 contains the record to be verifed/matched with the records in sheet no.2. we will match BTC_Name, BTC_Fname, Deg_RegNO of sheet1 with student name, father name, reg.no. of sheet no.2, if records of sheet no.1 are matched/presented in sheet no.2 then the whole row of sheet no.1 should be copied in to sheet no.3 (if matched display here) else other wise mismatched/ not presented records of sheet no.1 in sheet2 should be displayed (whole row) in sheet no.4 (not matched display here). i have shown sample values in sheet 3 and in sheet 4 taken from sheet 1. 
thanx.


----------



## slurpee55 (Oct 20, 2004)

Is the BTC_No a unique number? If it is, it would make for easier matching.


----------



## ks1 (Sep 17, 2008)

yes BTC_NO is unique but its is only in sheet no.1


----------



## jimr381 (Jul 20, 2007)

I would bring it into MS Access link the two tables together and then run a query on it. Access queries show the intersecting data, IE records that are in both.


----------



## ks1 (Sep 17, 2008)

so pls upload the access script for joining tables and then checking values.


----------



## slurpee55 (Oct 20, 2004)

Just for fun....
This formula, if entered in the column for verified names, will find the matches and leave blanks if the cell is not matched.

```
=IF(ISNA(INDEX('from this sheet'!C:D,MATCH('record to verified'!B3,'from this sheet'!C:C,0),1)),"MISSING",INDEX('from this sheet'!C:D,MATCH('record to verified'!B3,'from this sheet'!C:C,0),1))
```
Just to note, I started looking at B3 so I could make the match for Peter.


----------



## bomb #21 (Jul 1, 2005)

In column K of first sheet:

=B2&C2&H2

& copy down.

In column L of first sheet:

=MATCH(K2,'from this sheet'!H:H,0)

& copy down.

In column H of second sheet:

=C2&D2&B2

& copy down.

Then you can filter Sheet 1 and copy to Sheet 3 (does not equal #N/A) or Sheet4 (equals #N/A).

How you want this to work (e.g. one time or periodically) makes a difference though.


----------



## slurpee55 (Oct 20, 2004)

Ok, here is a much simpler formula that does the same thing that my previous one does...but it still leaves blanks if there is no match.

=IF(ISNA(VLOOKUP('record to verified'!B2,'from this sheet'!C:C,1,0)),"",'record to verified'!B2)


----------



## jimr381 (Jul 20, 2007)

ks1 said:


> so pls upload the access script for joining tables and then checking values.


I was out yesterday. There is no script you just do it.  You would link them based upon a common field. You specified that one of your fields was common to be data sets.


----------



## slurpee55 (Oct 20, 2004)

Alas (and arrrgh!) jim, the closest thing to a common field is the names - which the poster wants to match.
But 2 queries in Access would do the job - one for matched names and one for unmatched. I would suggest that you match on some combination, though - both the father and the son names, and wouldn't even guarantee that - who knows how many son Joe and father Bob (or whatever) might show up in the file?


----------

