Return value in cell above the reference

J

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
 
A

Ardus Petus

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

HTH
 
A

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)
 
D

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!
 
J

Jean

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