Microsoft Office Excel

Excel VLooup – Compare Two Spreadsheets & Return Value

This article covers:

How to use Excel VLooup function to compare columns in two spreadsheets, then return value if there are matched fields between the two columns.

A Common Scenario:

You have two spreadsheets: one has columns such as ID, Name and Phone Number, the other has ID, Name and Email Address; and you would like to combine this two spreadsheet to have ID, Name, Phone Number and Email Address.

VLooup Function Explained

Example Spreadsheet_1


Example Spreadsheet_2


Final Spreadsheet


Excel VLookup Function Parameters:

=VLOOKUP (lookup_value, table_array, col_index_number, [range_lookup])

The actual VLookup function we used to get above final spreadsheet:

=VLOOKUP(A2, 'Spreadsheet_2'!$A$2:$C$4, 4, FALSE)

VLookup Function Exmplained:

  • lookup_value: The first value to be looked up in Spreadsheet_1
  • table_array: The name of the second spreadsheet, in this case ‘Spreadsheet_2’
  • Lookup Range: since we are looking for values across the whole Spreadsheet_2, we should use $A$2:$C$4 instead of A2:C4. The ‘$’ sign is to ensure the lookup range is fixed
  • col_index_number: The column number from which Email will be copied from, in this case ‘3’
  • [range_lookup]: always use “FALSE’ for exact match

In case you wonder what would be the function if you’re using Google Sheet:

=VLOOKUP(B2, Sheet2!$A$1:$C$100, 3, 0)

# $ used is to ensure the lookup range won't change when you drag the cell down

Here you have it, happy VLOOKUPing!

By Ethan

To many, a business is a lifetime commitment. It's easy to start one yet difficult to make it successful. Attitude, skills, experiences and dedication help hone the craft along the way, but it's often the great vision and resilience to remain focused wins the game. Read more about me here