Excel VLooup – Compare Two Spreadsheets & Return Value

No Comments

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

ABC
1IDNamePhone
230023Chris11111111
3454Tim22222222
41003Sam33333333

Example Spreadsheet_2

ABC
1IDNameEmail
2454Timtim@example.com
31003Samsam@example.com
430023Chrischris@example.com

Final Spreadsheet

ABCD
1IDNamePhoneEmail
2 30023Chris11111111chris@example.com
3 454Tim22222222tim@example.com
41003Sam33333333sam@example.com

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

Here you have it, happy VLOOKUPing!

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

More from Our Blog

See all posts