If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
looking for another same value cell in a range and returning a coresponding value
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. |
#2
|
|||
|
|||
looking for another same value cell in a range and returning a coresponding value
=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" wrote in message oups.com... 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. |
#3
|
|||
|
|||
looking for another same value cell in a range and returning a coresponding value
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 wrote: =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" wrote in message oups.com... 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. |
#4
|
|||
|
|||
looking for another same value cell in a range and returning a coresponding value
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" wrote in message ups.com... 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 wrote: =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" wrote in message oups.com... 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. |
#5
|
|||
|
|||
looking for another same value cell in a range and returning a coresponding value
Anything you can recomend? Sugestions? Is there any way to exclude a
single cell from a range? Thank you for your help. Dawid Peo Sjoblom wrote: 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" wrote in message ups.com... 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 wrote: =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" wrote in message oups.com... 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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Returning Cell Value if someone deletes the contents of a cell | mmc308 | Worksheet Functions | 4 | March 31st, 2006 06:41 PM |
First row in Selection range (first index of a cell) EXCEL VBA | [email protected] | Worksheet Functions | 1 | March 20th, 2006 09:38 PM |
returning blank cell in criteria o | Joop | General Discussion | 3 | June 3rd, 2005 02:11 PM |
Find last cell in a range | Jason Morin | Worksheet Functions | 1 | June 3rd, 2004 04:19 PM |
Returning cell references | JaneG | Worksheet Functions | 3 | May 28th, 2004 09:17 PM |