0

This formula:

=INDEX(Q10:XFD10,MATCH(TRUE,INDEX((Q10:XFD10<>0),0),0))

looks up the first non-zero value from column Q till the end in row 10.

And it displays the value present in that cell. Suppose the cell found is V10.
I must display the value in cell V7.

I cannot change the range to lookup the first non-empty cell, because the higher rows are always filled.

There probably are two possible answers
One answer that looks up 3 rows higher (10-7=3) and another answer that looks up in the fixed row V$7.

I need the last option.

  • I think you mean XFD77? In any case, you should be able to use OFFSET() to return the value of the cell three rows before the cell found by the MATCH((). – Bandersnatch Aug 17 '17 at 16:23
  • Or you could just do this: =INDEX(Q10:XFD10,MATCH(TRUE,INDEX((Q10:XFD10<>0),0),0)-3) – Bandersnatch Aug 17 '17 at 16:32
  • OFFSET works, other suggestion fails. Using 3 - ROW() I get an offset that copies to other rows. All right, thanks. – Gerard Aug 17 '17 at 18:53
0

Suppose the cell found is V10. I must display the value in cell V7.

Then I'll propose a simple " if (row 10 is not zero ) & if( sum(from the left) = 0 ) " test will do, (assuming cell P10 is empty).

in Q7, put =IF(AND(Q10<>0,SUM($P$10:P10)=0),Q10,"")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.