Return value in cell above the reference

[Jean]

I've used the following formula to return the information I want to cel
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 i
correct.

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

Jea

 

[Ardus Petus]

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

HTH

 

[Ardus Petus]

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)

 

[Domenic]

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!

 

[Jean]

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