Lately there has been a lot of merging of worksheets in Excel at my work. Now, I don’t know why, but it seems that everyone is working in 10 or 15 different spreadsheets and keep on asking me to merge them. By merging they usually mean combining the columns based on a single column in one sheet.
For example, say I have two spreadsheets. One with Products and some information like color and UPC number. The other with products and some other information like length, width, and weight. The column that both sheets have in common is the product_id column. Now I want all that information in ONE spreadsheet.
So, the way to do this is with a simple Excel function called VLOOKUP.
Here’s the syntax (or how to use it) from Microsoft:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Lookup_value
The value to search in the first column of the table array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.). Lookup_value can be a value or a reference. If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value.Table_array
Two or more columns of data. Use a reference to a range or a range name. The values in the first column of table_array are the values searched by lookup_value. These values can be text, numbers, or logical values. Uppercase and lowercase text are equivalent.Col_index_num
The column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. If col_index_num is:
- Less than 1, VLOOKUP returns the #VALUE! error value.
- Greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.
Range_lookup
A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match:
- If TRUE or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.
- If FALSE, VLOOKUP will only find an exact match. In this case, the values in the first column of table_array do not need to be sorted. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, the error value #N/A is returned.
So now, for the example above, we need to make sure that the product_id columns are both in the first column in each worksheet – “A”. The VLOOKUP function needs that to be able to work correctly. We’ll then start off with:
=VLOOKUP(A2,
This means that we’re looking at the first column, 2nd row (A2).
Now we’ll put in the range that we are looking into. The second sheet will have the product_id in column “A”, and the height in column “B”. There 1000 lines in this sheet (including the header on row 1). So our code now looks like:
=VLOOKUP(A2, Sheet2!A2:B1000,
The neat thing here is that you can reference worksheets that aren’t even in the same file.
We’re going to modify that a bit so that the Table_Array is fixed for when we drag this function down through the cells later on. The way you “fix” the range is to add dollar signs ($) before each of the cell references in the function:
=VLOOKUP(A2, Sheet2!$A$2:$B$1000,
The third and forth things are pretty simple so we’ll combine them into one step here. The 2 means that we want to return the value for the 2nd column where the first columns (product_id) match up. The 0 (or false) means that we want to have the function find an EXACT match for the product_id. Keep in mind that EXACT means EXACT. If there are spaces at the end of the product_id in one worksheet and not the other the fields will NOT MATCH EXACTLY.
So here’s the final VLOOKUP code:
=VLOOKUP(A2, Sheet2!$A$2:$B$1000, 2, FALSE)
Remember that these are references and sometimes you’ll need to copy and paste-special with just the values to keep the references static. If not you just have to make sure whatever you are referencing is always available to this spreadsheet function.