-2

I have a question regarding a simple formula: Gn=Fn-En, but if En is an empty cell, then search on the same row (n), all the n-1 values in order to find the first non empty cell. Is it possible to make such formula but without having any vba code?

Regards,

BogdanM
  • 957
  • 4
  • 15
  • 32

2 Answers2

1
=LOOKUP(2,1/(B5:E5<>""),B5:E5)

will work for any single-row or single-column range

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
0

You can get the last non-empty cell value in a row with hlookup.
For row 5, if values are numeric:
=HLOOKUP(10^10,5:5,1,1)
if values are text:
=HLOOKUP(REPT("z",50),5:5,1,1)
HTH

kindoflost
  • 91
  • 1
  • 1
  • 7