Excel: Return value of different cell in same row based on a 3rd cell value

microsoft excelmicrosoft-excel-2010worksheet-function

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?

Best Answer

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.

Related Question