• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 510
  • Last Modified:

Excel Match Column A,B,and C to A,B,and C on another sheet

Is there a function that I can match column A, B, and C, to A,B,and C on another sheet and if there is a match true=yes if false then No?  I have entered 850 lines of data into a database and I want to cross reference what I entered into the master and make sure everything is there and that it was entered correctly.
1 Solution
Hello sharris_glascol
pls find attached a sample of this in action.
An incorrect match yields an "N/A"

here is the code

Open in new window

If you have entered the data into a database, then surely you can link the table in the database to the excel file on columns ABC. If the number of lines which appear in a select query with all fields joined then your transfer was successful.

You could also use this formula in D:
Seen attached.
sharris_glascolAuthor Commented:
not sure this does what I want what I need is to see is that if columns A is customer number 123 column b is there item abc and column c is there price.  Need to make sure that those three columns together match on another sheet.
Hi sharris_glascol,

The easiest way to do this via formula is to add a helper column to your new master sheet. This column should contain your columns A, B, and C concatenated together with a delimiter; then use the same format on your source file to verify its existence on the master sheet.


On your new master sheet, assuming your data in A, B, and C starts in row 2, paste this into cell D2:
Then fill down through the bottom of your data.


Then, on your source file, enter the following into D2:
=NOT(ISERROR(MATCH(A2&CHAR(7)&B2&CHAR(7)&C2,'[Book2]new master sheet'!$D$2:$D$851,0)))
Don't forget to change the reference to your new master file, then fill down through the bottom of your data.
@The_Barman   Nice!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now