# Formula to merge column from sheetA to sheetB to matching ID

Match unique id Sheet1!A2 to a cell in Sheet2!A2:A100 that has the matching ID.

Paste cell content from column Sheet2!C2:C100 from the same row with the matching id in Sheet2 to the cell with formula in it.

What is the formula for this (or how do you do this)?

The following did not work:

``````=VLOOKUP(Sheet1!A2,Sheet2!A2:A1000, Sheet2!C2:C1000)
``````
-
Your third parameter should be a number (not a range) indicating which column in the second parameter (a range) you want to access if you find a match. You should also use a 4th parameter to specify that you want an exact match or the closest match. – Mike Fitzpatrick Sep 11 '12 at 5:19
how will the 3rd parameter, a number, indicate "which column in the 2nd parameter" to access since a number indicates a specific cell, but i want to check the whole column. it looks like the formula in the answer is using a range, not a number – kittensatplay Oct 4 '12 at 18:21

If you would like to find the value in column C when the value in column A matches the ID in `Sheet1!A2` then:

``````=INDEX(Sheet2!\$C\$2:\$C\$1000,MATCH(Sheet1!\$A\$2,Sheet2!\$A\$2:\$A\$1000,0))
``````
-
i opened a new excel window, pasted this code, and this made excel freeze (not responding) -- ill try again later – kittensatplay Oct 4 '12 at 18:33
this didnt work, a clarification is that Sheet1 or Sheet2 means a sheet within an excel window – kittensatplay Oct 4 '12 at 20:46

In conjunction with say a Column letter a number may indeed indicate a specific cell but the syntax for =VLOOKUP is VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) (ref) so in this context a number indicates an entire column (1 for `A` [in this instance, since the index field is in `ColumnA`], so `3` for `C`).

A fourth parameter (range_lookup) is generally advisable (usually `FALSE` but `0` serves) to ensure matching of index is exact and not just 'next best'. In an array ordered ascending for lookup_value, if the fourth parameter is omitted, `TRUE` or `1` the formula would return the next largest value that is less than lookup_value, where there is no exact match. Where not ordered 'next best' could be almost any of the values in the (here third) column.

With `FALSE` or `0` the array does not need to be ordered.

It may be worth repeating that the col_index_num cannot be negative.

-