# Looking for Excel formula that return the value of the first cell in a row for last non-empty cell in different row

I have spreadsheet like this one:

``````a b c d e f
1     1
1 1   1 1
1 1 1
``````

I'm looking for a formula that will return d, e and c:

`````` formula   a b c d e f
d      1     1
e      1 1   1 1
c      1 1 1
``````

I have tried something like this

=INDEX(\$O\$1:\$XFD\$1;1;MATCH(VLOOKUP(2;1/(O2:XFD2<>"");O2:XFD2);O2:XFD2;0))

but it doesn't work whatsoever.

EDIT for Rajesh S

As you can see I found the solution

=INDEX(\$A\$1\$:\$XFD\$1;1;LOOKUP(2;1/(O2:XFD2<>"");COLUMN(O2:XFD2)))

• I use VBA user-defined function when I need to solve such task. The code will be simple enough. Commented Sep 24, 2019 at 11:45
• Please Edit your post & share proper Screen Shot containing Col Name & Row Number,,, Check C hasn't last empty cell bank!! Commented Sep 24, 2019 at 11:59
• I got it, it's `=INDEX(\$A\$1\$:\$XFD\$1;1;LOOKUP(2;1/(O2:XFD2<>"");COLUMN(O2:XFD2)))` Commented Sep 24, 2019 at 19:13