Excel - find values in a column except for value in current row

0
votes

I have a workbook as follows ...

     Col# -> 1     2           3       

 Row #
   1         5     London      
   2         6     Paris   
   3         4     New York  
   4         2     Joburg  
   5         1     Oslo  
   6         5     Rio

What I want to do is, for each cell in column 3, return a column 2 entry ONLY when another column 1 value matches the value of the current row (otherwise return nothing). For example, in Row 1, Col 3, I want see 'Rio' i.e. match row 1, col 1 with another same value in column 1 as the row 1 (i.e. '5') and return column 2 value (Rio). And in row Row 6, Col 3, I want see 'London' by matching row 1, col 6 with with another same value of column 1 of the the Rio row (i.e. '5') and return column 2 value London.

I'm been wrestling with this and I think it needs some kind of match/find formula to match values in column 1 EXCEPT for the current row. i.e. when evaluating row 1 col 3, I only want to find the '5' in row 6, col 1, and then return Rio, not find the '5' of the current row where the formula is being calculated. VLOOKUP and INDEX/MATCH will either find the first value or all of the values, (neither of which I want)

2
excelexcel-formula
Just to clarify, you want the values in new column i.e. column 4, right? – ManishChristian
If there are multiple other rows besides the one you are on, do want to pull back all of them? – Alan Waage
Manish, Yes, add values in to a new column, 4. – timo
Alan, in my use case there should never be more than two numbers the same in column #1, so only need to find the other value that is the same – timo

2 Answers

0
votes

This code is sample for just six rows:

Sub TEST()

For i = 1 To 6
For j = 1 To 6

If i <> j Then

If ActiveSheet.Cells(j, 1).Value = ActiveSheet.Cells(i, 1).Value Then
ActiveSheet.Cells(i, 3).Value = ActiveSheet.Cells(j, 2).Value

End If
End If
Next j
Next i



End Sub

Try and let know if you need any more help.

0
votes

As your data in column A will never have more than two instances of one value, here is the formula that I come up with: 

=IF(
COUNTIF($C$1:$C1,LOOKUP(2,1/($A$1:$B$10=$A2),$B$1:$B$10))<1,
LOOKUP(2,1/($A$1:$B$10=$A2),$B$1:$B$10),
INDEX($A$1:$B$10,MATCH($A2,$A$1:$A$10,0),2)
)

Place this formula in cell C2 and drag it down and you will get your result, like this:

enter image description here

Let me know if you find any issue.