How to Compare Data in Excel with VLOOKUP

Use VLOOKUP to simplify the task of verifying that your data entry is consistent.

Excel 2010’s VLOOKUP function is designed to search for a given value in the first column of a data table and return the value from another column located on the same row as the value for which you searched. You can also use VLOOKUP to compare data in two columns to ensure that entries is the first column also exist in the second.

Instructions

1. Open the Excel 2010 spreadsheet that contains that data you want to compare. Click on the top cell of the first empty column in your spreadsheet.

2. Enter the following formula into the cell:

   =vlookup(A1,B:B,1,False)

   Change "A1" to the top cell in the first column of data. Change the "B"s in "B:B" to the letter of the column where the second set of data is located. Keep the "1," as this tells VLOOKUP that you want to check the first column in the "B:B" range. "False" specifies that you want to search for an exact match, instead of an approximate one.

3. Press "Enter" to complete the formula and have Excel run it. If the first entry in the first column appears in your second column, its name appears in the cell where you wrote the formula. If not, "#N/A" appears in the cell.

4. Select the cell where you entered your formula. Move your mouse pointer to the bottom-right corner of the cell. Click and hold the mouse button, then drag the mouse down until you reach the last cell where you want to use the formula. Release the mouse button. When you copy formulas like this, Excel will use relative references, so the "A1" in the formula will change to, for example, "A2," "A3" or "A4" depending on what row the formula is in. The column where you entered the formulas will now display all the items from the first column that also appear in the second.

References

• Microsoft Support: How to Use VLOOKUP or HLOOKUP to Find an Exact Match