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

Question

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?

Answer 1

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.

Answer 2

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