Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 1 down vote favorite

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)
share|improve this question
1  
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

2 Answers

up vote 1 down vote

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))

share|improve this answer
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
up vote 0 down vote

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.

SU472583 example

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.