Reverse Match & ISBLANK

[BiggusDoggus]

Hi

I have {=IFERROR(MATCH(FALSE,ISBLANK(B14:AM14),0),"")} to return the position of the first non-blank cell in that row.

What I would like to do is return the position of the last non-blank cell in that row.

I tried {=IFERROR(MATCH(FALSE,ISBLANK(AM14:B14),0),"") - that was, of course, far too simple and logical to actually work, as Excel automatically swaps the B & AM around.

Any help greatly appreciated thanks!

 

[James006]

Hi,

You can test following:

=SUMPRODUCT(MAX((COLUMN(A14:AM14))*(B14:AM14<>"")))

Hope this will help

 

[DanteAmor]

Hi @James006, It should be B

=SUMPRODUCT(MAX((COLUMN(B14:AM14))*(B14:AM14<>"")))

Annex a couple more options.

=LOOKUP(2,1/(B14:AM14<>""),COLUMN(B14:AM14))

And Array formula:

{=MAX(IF(B14:AM14<>"",COLUMN(B14:AM14)))}

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself