# Excel formula to get first non-empty value and then return value other row

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

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