| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
return adjacent cell's value based on formula
I have a formula that returns a result...
=MAX(IF($C$2:$C$14613=N$35,IF($A$2:$A$14613=$L37, $E$2:$E$14613))) How do I return the value of the cell that is located immediately to the left of cell that contains the value produced by the formula above? In other words, if the formula returns a value that is found in cell $C $17, I want to know what value is in cell $C$16. I thought the solution may involve offset(), but I cannot seem to make it return the correct result so I think that is the wrong approach. Any ideas? Thanks! Joseph |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
return adjacent cell's value based on formula
Well, your description is "all over the place"!
=MAX(IF($C$2:$C$14613=N$35,IF($A$2:$A$14613=$L37 ,$E$2:$E$14613))) First, your formula returns a value from column E. Then you say: return the value of the cell that is located immediately to the left of cell that contains the value produced by the formula above Ok, so if the formula returns a value from column E then you want this new formula to return a value from column D. But then you say: if the formula returns a value that is found in cell $C$17, I want to know what value is in cell $C$16. Ok, column C is not to the immediate left of column E and C16 is *above* C17 and not to the immediate left! So, when you figure out what you want I'm sure we can come up a formula to do it! -- Biff Microsoft Excel MVP "icystorm" wrote in message ... I have a formula that returns a result... =MAX(IF($C$2:$C$14613=N$35,IF($A$2:$A$14613=$L37, $E$2:$E$14613))) How do I return the value of the cell that is located immediately to the left of cell that contains the value produced by the formula above? In other words, if the formula returns a value that is found in cell $C $17, I want to know what value is in cell $C$16. I thought the solution may involve offset(), but I cannot seem to make it return the correct result so I think that is the wrong approach. Any ideas? Thanks! Joseph |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
return adjacent cell's value based on formula
Biff,
You're correct. I gave a bad example with incorrect syntax. I want to know what value is in the cell located one column left of the result returned from column E. So, if the value returned from the formula... =MAX(IF($C$2:$C$14613=N$35,IF($A$2:$A$14613=$L37, $E$2:$E$14613))) ....is located in cell E10, I would like a formula to return the value in cell D10. Thanks... J On Feb 2, 3:38*pm, "T. Valko" wrote: Well, your description is "all over the place"! =MAX(IF($C$2:$C$14613=N$35,IF($A$2:$A$14613=$L37 ,$E$2:$E$14613))) First, your formula returns a value from column E. Then you say: return the value of the cell that is located immediately to the left of cell that contains the value produced by the formula above Ok, so if the formula returns a value from column E then you want this new formula to return a value from column D. But then you say: if the formula returns a value that is found in cell $C$17, I want to know what value is in cell $C$16. Ok, column C is not to the immediate left of column E and C16 is *above* C17 and not to the immediate left! So, when you figure out what you want I'm sure we can come up a formula to do it! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
return adjacent cell's value based on formula
Try this array formula** :
=INDEX($D$2:$D$14613,MATCH(MAX(IF($C$2:$C$14613=N $35,IF($A$2:$A$14613=$L37,$E$2:$E$14613))),IF($C$2 :$C$14613=N$35,IF($A$2:$A$14613=$L37,$E$2:$E$1461 3)),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "icystorm" wrote in message ... Biff, You're correct. I gave a bad example with incorrect syntax. I want to know what value is in the cell located one column left of the result returned from column E. So, if the value returned from the formula... =MAX(IF($C$2:$C$14613=N$35,IF($A$2:$A$14613=$L37, $E$2:$E$14613))) ....is located in cell E10, I would like a formula to return the value in cell D10. Thanks... J On Feb 2, 3:38 pm, "T. Valko" wrote: Well, your description is "all over the place"! =MAX(IF($C$2:$C$14613=N$35,IF($A$2:$A$14613=$L37 ,$E$2:$E$14613))) First, your formula returns a value from column E. Then you say: return the value of the cell that is located immediately to the left of cell that contains the value produced by the formula above Ok, so if the formula returns a value from column E then you want this new formula to return a value from column D. But then you say: if the formula returns a value that is found in cell $C$17, I want to know what value is in cell $C$16. Ok, column C is not to the immediate left of column E and C16 is *above* C17 and not to the immediate left! So, when you figure out what you want I'm sure we can come up a formula to do it! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
return adjacent cell's value based on formula
Thank you, Biff. That works perfectly.
I use INDEX and MATCH routinely. I'm not sure why I didn't think of using it in this case. Anyway, great work! J On Feb 2, 6:34*pm, "T. Valko" wrote: Try this array formula** : =INDEX($D$2:$D$14613,MATCH(MAX(IF($C$2:$C$14613=N $35,IF($A$2:$A$14613=$L37*,$E$2:$E$14613))),IF($C$ 2:$C$14613=N$35,IF($A$2:$A$14613=$L37,$E$2:$E$146 1*3)),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
return adjacent cell's value based on formula
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "icystorm" wrote in message ... Thank you, Biff. That works perfectly. I use INDEX and MATCH routinely. I'm not sure why I didn't think of using it in this case. Anyway, great work! J On Feb 2, 6:34 pm, "T. Valko" wrote: Try this array formula** : =INDEX($D$2:$D$14613,MATCH(MAX(IF($C$2:$C$14613=N $35,IF($A$2:$A$14613=$L37*,$E$2:$E$14613))),IF($C$ 2:$C$14613=N$35,IF($A$2:$A$14613=$L37,$E$2:$E$146 1*3)),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
return adjacent cell's value based on formula
Basically, MATCH should return the cells place within a range.
The address function can be used in order to return the cells address. From here, using OFFSET is "a piece of cake". Micky "icystorm" wrote: I have a formula that returns a result... =MAX(IF($C$2:$C$14613=N$35,IF($A$2:$A$14613=$L37, $E$2:$E$14613))) How do I return the value of the cell that is located immediately to the left of cell that contains the value produced by the formula above? In other words, if the formula returns a value that is found in cell $C $17, I want to know what value is in cell $C$16. I thought the solution may involve offset(), but I cannot seem to make it return the correct result so I think that is the wrong approach. Any ideas? Thanks! Joseph . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula to populate data in a cell based on another cell's color | Excel Discussion (Misc queries) | |||
| HOW DO I GET A CELL TO RETURN A NEIGHBORING CELL'S VALUE | Excel Worksheet Functions | |||
| using a cell's reference to return a value. | Excel Worksheet Functions | |||
| CHANGE ONE CELL'S VALUE AND GET THE SAME RETURN - SOME HELP PLEASE | Excel Discussion (Misc queries) | |||
| Change value based on another cell's value | Excel Discussion (Misc queries) | |||
Hybrid Mode
