Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Returning Cell Value if someone deletes the contents of a cell | Excel Worksheet Functions | |||
First row in Selection range (first index of a cell) EXCEL VBA | Excel Worksheet Functions | |||
Returning Macro to Starting Cell | Excel Worksheet Functions | |||
returning blank cell in criteria o | Excel Discussion (Misc queries) | |||
Returning a Value to a Cell Based on a Range of Uncertain Size | Excel Worksheet Functions |