Compare Multiple Fields with Excel vlookup (Easy)

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:

Excel vlookup 1

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. Excel vlookup 2 (combine with &)

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.

Excel vlookup 3

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.

Excel vlookup 4 - combine with spaces

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

Excel vlookup 5 - combine with slashes

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

My Favorite Windows Software

11 Comments

Filed under Audit, Data Analytics, Excel, How to...

11 responses to “Compare Multiple Fields with Excel vlookup (Easy)

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

    Like

  2. jamezjrs

    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.

    Like

    • 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!

      Like

  3. Prateek

    excellent!!

    Like

  4. Ankur

    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?

    Like

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

      Like

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

        Like

  5. Jaap

    Thanks, worked for me!

    Like

Leave a Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s