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.
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.
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: