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:
-
I have one worksheet (#1) with only two dozen rows. Out of one column with unique content (ID's) I want to compare:
-
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).
-
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
andMATCH
for your example, it would look like this: (in
D1
)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.