When you need to determine whether several fields in 2 Excel documents (or tabs) match, all you need to do is combine the fields in each document into one value and then compare the 2 values using vlookup.
You could do this many ways, but if you’re new to Excel formulas, I think this way is easier to configure and understand. I’m assuming you’re familar with the basics of Excel and vlookup already.
If you are not familiar with vlookup, you might want to review this first, as my post does not teach you vlookup, just another way to use it.
Assume you need to determine whether the access assigned to user IDs changed since the last time you reviewed them, and you have 2 Excel spreadsheets, both in the C:\Project folder on your hard drive. The data in both spreadsheets is on a tab called Sheet1. Here’s how the access was last year and this year:
Here’s how to compare the 2 files:
1) Enter the following combine formula in cell D2 of each file: =A2&B2&C2 and copy the formula down the column.
See the result below (only Last Year.xlsx is shown, but do the same for This Year.xlsx). Note that the same formula in cell D2 is shown above column D in the formula (fx) field. 
2) In This Year.xlsx, enter the following formula in cell E2: =VLOOKUP(D:D,‘C:\Project\[Last Year.xlsx]Sheet1′!$D:$D,1,FALSE) and copy the formula down the column.
See the result below, and note that the same formula in cell E2 is shown in the formula (fx) field.
3) The “N/A” in cell E3 indicates that the value in cell D3 is not the same in This Year.xlsx and Last Year.xlsx. If you compare cell E3 in the examples above, you can see that the “Read” permission in Last Year.xlsx changed to “Update” in This Year.xlsx. That’s the type of change you want to identify.
Getting Fancy
If you dislike jamming the contents of cells A, B, and C together, you can add spaces or other characters to the formula for better readability.
Add spaces between cell contents. If you change the combine formula shown in step 1 to the formula shown below in the fx field, spaces will be inserted between the values in column D. Note that you have to put quotes around the space character.
Also, remember to put the “&” between all items you’re combining, the cells and the spaces. If you leave a “&” out, you’ll get an error.
Add slashes between cell contents. If you change the combine formula to the one shown below, slashes will be inserted between the values. Note that you have to put quotes around the slashes (or any text you insert between fields that you combine).
The vlookup will work the same with spaces, slashes, or whatever you put in there, as long as you insert the same character in both files. Don’t add spaces to one file, and add slashes to another file, and then try to compare them–they won’t match. Even if the original data is the same, the vlookup will indicate none of the items match, so be consistent.
If vlookup confuses you, you’re not alone. It took me a while to get comfortable with it. Many of the descriptions on the ‘Net are not easy to follow or understand. Maybe I will do a post with step-by-step instructions on how to do the vlookup shown above. Let me know if you’re interested.
Other posts regarding Excel, other tools, and tips:
Download Domino Database to Excel w/o Rights
Convert Report Headings into List
PSPad: Great Text File Audit Tool
ITauditSecurity 
I always marvel when a post gets LOTS of hits, but no comments. Sure, if you like my stuff, you can send me BITCOIN, but all I ask is a occasional howdy or thanks, or even how I can’t spell or am secretly a real idiot.
LikeLike
yup
LikeLike
That’s definitely a start. A poor one, but a start nonetheless.
LikeLike
I really liked the post. Does the syntax ‘D:D’ in the vLookup formula mean the table array is all of column D? I’m new to vLookups but am getting better with each one I do. This is a good way to utilize it. Thanks for the post.
LikeLike
jamezjrs,
Glad the post helped you. Yes, ‘D:D’ means the formula uses all the cells in column D. If instead you selected only cells D10 through D20, it would appear as ‘D10:D20’.
Believe or not, I work with auditors with lots of experience who cannot do a vlookup. You’re inching ahead of the pack!
LikeLike
excellent!!
LikeLike
Glad you liked it!
LikeLike
How could you have a single formula apply to a larger file?
For eg – if the file had more entries, how could you have used the combination formula applied to all the entries?
LikeLike
Ankur,
Not sure I understand your question….
What I did above is put the formula in one cell and then copied it down the entire column. For example if the formula is in cell D2, highlight cells D2 and all the cells below it in that column, then hold down CTL and press the D key to copy down.
LikeLike
Ankur,
I thought about this some more. If you’re asking whether you can combine the contents of more cells together and compare them, the answer is yes. I’m not sure what the limit is, but you can combine more than 3 cells.
LikeLike
Thanks, worked for me!
LikeLike
Pingback: Auditors, Do Data Analytics or Die | ITauditSecurity