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

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

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


.

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!

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!

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

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