looking for another same value cell in a range and returning a coresponding value

[dawid72]

I have been struggling for a few days with this problem and still can't
find a solution...
This is what I am trying to achieve:
1. Find another cell in column 2 with the same value as starting cell
located in the same column 2 (not second, not third and not last same
value, there are going to be just two cells with the same value and I
am looking for that other one).
2. Check value from column 3 corresponding with that cell,
3. Return this value into column 4 at the row of the starting cell.
I don't think that I am doing a great job explaining it so let me put
an example.

Referring to data shown below:
6 teams are paired in a tournament. Pair numbers are being recorded in
column 2. Column 3 represents points gained by each team and column 4
(this is what I am looking for) is supposed to show points gained by
the opponent - another team from the same pair (same value in column
2).
Starting at cell A2:
A2 value is 1 and another cell in this column with the same value is
D2. Points gained by team 4 (row D) are represented in corresponding
cell D3. I would like this value to go into the A4 cell - and represent
points gained by the opponent.
Same problem for other cells - starting at E2 I would like to insert C3
value into E4 cell.

1 2 3 4

A team1 1 A D
B team2 2 B F
C team3 3 C E
D team4 1 D A
E team5 3 E C
F team6 2 F B

I have tried LOOKUP and VLOOKUP but column 3 values are not going to be
sorted and I was not able to (for example) exclude the row, at which I
start my formula. I am clearly no able to come up with a complex
procedure that would do that... Please help.

 

[Peo Sjoblom]

=INDEX($B$3:$Z$3,MATCH(A2,$B$2:$Z$2,0))

although I am not really sure how your data looks

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

 

[dawid72]

Thanks,

....but I am afraid this will work only for the first cell.
How about the other ones? How about starting in the middle of the
column and begin able to search what's above and below the starting
cell.

"...
Same problem for other cells - for example starting at E2, I would like
to automatically insert C3 value into E4 cell.

1 2 3 4
A team1 1 A D
B team2 2 B F
C team3 3 C E
D team4 1 D A
E team5 3 E C
F team6 2 F B
...."

 

[Peo Sjoblom]

You should change the design of your table, it would be very difficult for a
formula to do this if the lookup values can be anywhere and even within the
lookup range

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

 

[dawid72]

Anything you can recomend? Sugestions? Is there any way to exclude a
single cell from a range?

Thank you for your help.

Dawid