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

	A	B	C
1	ID	Name	Phone
2	30023	Chris	11111111
3	454	Tim	22222222
4	1003	Sam	33333333

Example Spreadsheet_2

	A	B	C
1	ID	Name	Email
2	454	Tim	tim@example.com
3	1003	Sam	sam@example.com
4	30023	Chris	chris@example.com

Final Spreadsheet

	A	B	C	D
1	ID	Name	Phone	Email
2	30023	Chris	11111111	chris@example.com
3	454	Tim	22222222	tim@example.com
4	1003	Sam	33333333	sam@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

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!