Formula Last Cell value in row but placed in the same column

[sswcharlie]

Hi

Need a formula to enter across a row, say A 12 to H 12 to get last used cell in a row A4 to H4


If the range has figures in columns A to c and not in d, e, or h. The result required is the figure from Col c

So in row 12 their will only be one figure,the value of C4all other cells will be blank.

Getting the last cell in row I can use
=OFFSET(<StartCell>,0,MATCH(MAX(Range)+1,<Range>,1)-1) but not sure how to get the answer in the specific columns of last row value.

What is the best approach to get the answer?
Thanks
CharlesHarris

[p45cal]

in A12:
=IF(AND(COUNTA(B4:$H4)=0,COUNTA(A4)=1),A4,"")
copy to the right.

[sswcharlie]

Hi P45cal

Thanks for your formula. Have tried with no luck. Have noticed in my notes above that it should have read A to c and not in d, e,F,G, or h (had missed out F,G)
In your formula you refer to B4:$H4 , should that be A4:$H4 ?
Thanks for your help.
Regards
Charles
downunder in NZ

[p45cal]

Thanks for your formula. Have tried with no luck. Have noticed in my notes above that it should have read A to c and not in d, e,F,G, or h (had missed out F,G)I 'd taken that as a given.




In your formula you refer to B4:$H4 , should that be A4:$H4 ?No.

Upload a tiny file with it not working.

[sswcharlie]

13941


Hi P45cal


Attached is a workbook as requested.

Thankyou

Charles Harris

[mikerickson]

Perhaps
=MATCH(E9+314,A4:H4)
or
=LOOKUP(E9+314,A4:H4)
will do what you want, if the cells contain numbers.

For text =LOOKUP("zzzzz",A4:H4)

[p45cal]

You didn't follow insrtructions; although the formula in A12 is correct, you didn't simply copy to the right. You seem to have adjusted the formula in B12 and then copied that to the right.
Since testing this, I see it doesn't work and return the number if there is one oin column H, so a small tweak will put that right as long as there is never anything in column I:
=IF(AND(COUNTA(B4:$I4)=0,COUNTA(A4)=1),A4,"")
in A12 and copy to the right.