return adjacent cell's value based on formula

[icystorm]

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

 

[T. Valko]

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!

 

[מיכ×ל (מיקי) ×בידן]

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]

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

 

[T. Valko]

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$14613)),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


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

 

[icystorm]

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

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


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