Return value in cell above the reference

I've used the following formula to return the information I want to cell N1434:

=SUMPRODUCT(--($Y$2:$Y$1404=$Y1434),--($E$2:$E$1404=$E1434),--($F$2:$F$1404=$F1434),N$2:N$1404)

In this instance it returns the value from cell N773, which is correct.

Now in Cell N1433 I would like to return the value in cell N772. I can't use the sumproduct function because the references can't be used again without throwing off other calculations. I tried to get the row function to go out and return N773 as an answer to something but failed miserably. Any help is appreciated.

Jean

What if the returned value is that in cell N2?
Do you want the contents of cell N1 to be returned?

HTH
--
AP

"Jean" <[email protected]> a écrit dans le
message de news: [email protected]...
>
> I've used the following formula to return the information I want to cell
> N1434:
>
> =SUMPRODUCT(--($Y$2:$Y$1404=$Y1434),--($E$2:$E$1404=$E1434),--($F$2:$F$1404=$F1434),N$2:N$1404)
>
> In this instance it returns the value from cell N773, which is
> correct.
>
> Now in Cell N1433 I would like to return the value in cell N772. I
> can't use the sumproduct function because the references can't be used
> again without throwing off other calculations. I tried to get the row
> function to go out and return N773 as an answer to something but failed
> miserably. Any help is appreciated.
>
> Jean
>
>
> --
> Jean
> ------------------------------------------------------------------------
> Jean's Profile:
> http://www.excelforum.com/member.php...o&userid=17585
> View this thread: http://www.excelforum.com/showthread...hreadid=547093
>

You can use $N$1:$N$1403 as returned range:

=SUMPRODUCT(--($Y$2:$Y$1404=$Y1434),--($E$2:$E$1404=$E1434),--($F$2:$F$1404=$F1434),N$1:N$1403)


--
AP

"Jean" <[email protected]> a écrit dans le
message de news: [email protected]...
>
> I've used the following formula to return the information I want to cell
> N1434:
>
> =SUMPRODUCT(--($Y$2:$Y$1404=$Y1434),--($E$2:$E$1404=$E1434),--($F$2:$F$1404=$F1434),N$2:N$1404)
>
> In this instance it returns the value from cell N773, which is
> correct.
>
> Now in Cell N1433 I would like to return the value in cell N772. I
> can't use the sumproduct function because the references can't be used
> again without throwing off other calculations. I tried to get the row
> function to go out and return N773 as an answer to something but failed
> miserably. Any help is appreciated.
>
> Jean
>
>
> --
> Jean
> ------------------------------------------------------------------------
> Jean's Profile:
> http://www.excelforum.com/member.php...o&userid=17585
> View this thread: http://www.excelforum.com/showthread...hreadid=547093
>

Try...

=INDEX(N$2:N$1404,MATCH(1,IF($Y$2:$Y$1404=$Y1434,IF($E$2:$E$1404=$E1434,I
F($F$2:$F$1404=$F1434,1))),0)-1)

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article <[email protected]>,
Jean <[email protected]> wrote:

> I've used the following formula to return the information I want to cell
> N1434:
>
> =SUMPRODUCT(--($Y$2:$Y$1404=$Y1434),--($E$2:$E$1404=$E1434),--($F$2:$F$1404=$F
> 1434),N$2:N$1404)
>
> In this instance it returns the value from cell N773, which is
> correct.
>
> Now in Cell N1433 I would like to return the value in cell N772. I
> can't use the sumproduct function because the references can't be used
> again without throwing off other calculations. I tried to get the row
> function to go out and return N773 as an answer to something but failed
> miserably. Any help is appreciated.
>
> Jean

Both solutions worked! Thank you so much--you are the rock stars of the excel universe!
Jean