Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

What I am looking for is not an ambigious "if" return while also "search" and "find" are not advanced enough to fulfill my slightly complicated but easy-to-understand goal:

  1. I have one worksheet (#1) with only two dozen rows. Out of one column with unique content (ID's) I want to compare:

  2. Those unique ID's are also to be found in another - the original - worksheet (#2), which is much larger. When creating worksheet No. 1 I did not anticipate that I need another part of the content (i.e. another cell content information out of the corresponding original row).

  3. Now I want to return just that content. I don't want to modify it and it is not dependent on and "if"-clause, I just want to simply return it from #2 to another new column in my worksheet #1.

TL;DR: I wish to find a cell value in row $Fx of worksheet #2 by comparing it (e.g. "SPG123", generally value $Bx) from worksheet #1 and then return cell-value $Cx (unaltered) to cell $Dx in worksheet #1

So what I actually look for is cell-value $Cx. I could just search for the unique string (e.g. "SPG123"), but with several dozen of those, this takes too long, when I could also just automate it.

How do I do that?

share|improve this question
1  
You'll need to use Macros. Have you tried that yet? How far did you get? Where are you getting stuck? –  techie007 Aug 26 '12 at 14:55
add comment

2 Answers

You can use the VLOOKUP function from your functions wizard with the ID column (in table #1) as index. The Vlookup function will match the ID in table #2 and you place in offset parameter the table 2 column number where is the data you want to retrieve from table 2 to 1

So adapting to your cell numbers:

Your column F in sheet 2 has to be moved or copied before column C (because it is the base and the offset is to the right of the base column)

You enter the formula (using wizard or not) in cell $Dx of worksheet #1 using:

Index (search) value will be cell $Bx from #1

Data to search will be the whole sheet #2 from the previous column F relocated before C to column C (probably moved to D because you inserted a column before it)

Offset will be the number of columns you need to move from the index column to the data you want column.

Anyways, my explanation looks confuse to me and you probably will understand better reading:

office-help or an example

share|improve this answer
add comment

if you can't move the columns, then you would have to use a combination of INDEX and MATCH

for your example, it would look like this: (in D1)

=INDEX(Sheet2!C:C,MATCH(B1,Sheet2!F:F,0))

This can then be dragged down to other cells (if you need to start at somewhere other than D1, alter the B1 in the example). This will return #N/A if the value cannot be found.

share|improve this answer
add comment

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.