途中に空白セルがあっても取得でき、文字列だけでなく数値も取得できます。
TAKE/TRIMRANGE関数を使う方法
データの先頭や末尾の空白を一括削除できるTRIMRANGE関数を使う方法です。
ただし使用できるのがMicrosoft 365バージョンに限られますので、使えない場合は別の（XLOOKUP関数以降の）方法を試してください。
次の画像ではD3セルに数式を入力し、B3:B12の範囲のうち（空白セル以外で）最も下にある「すいか」を取得しています。
D3セル
=TAKE(TRIMRANGE(B3:B12),-1)
TRIMRANGE関数でB3:B12の範囲内の先頭と末尾にある空白セルを除き（ただし先頭のB3セルには値があるので、実際に除かれるのはB11,B12セルの2つ）、TAKE関数で残ったうちの最後のセル（第2引数=-1）を取得する、という内容です。
TRIMRANGE関数と同じ機能を持つ「トリム参照」が導入されていますので、これを利用して次のようにすることもできます。
D3セル
=TAKE(B3.:.B12,-1)
「.:.」がトリム参照の部分で、これによりB3:B12の範囲のうち先頭と末尾の空白セルが除かれます。
ただし、この記事の内容では先頭部分の空白セルを考慮する必要がないので「.:.」を「:.」に代えても（末尾の空白セルのみ削除する）OKです。
注意点として、TRIMRANGE関数やトリム参照では空文字列（""）を除くことができないのでデータの内容によっては意図した結果にならない場合があります。
次の画像では末尾（B11セル）に空文字列があるので、これが取得されています。
空文字列を取得したくない場合は、以降のTAKE/FILTER関数を使う方法や、それ以降の方法を試してみてください。
ついでにTRIMRANGE関数の代わりにTOCOL関数を使う例を紹介します。
TOCOL関数は表を縦1列に並べ替える関数ですが、同時に空白セルを除くことができるので、次の画像ではこれを利用して上記例と同じ結果を得ています。
D3セル
=TAKE(TOCOL(B3:B12,1),-1)
TOCOL関数の第2引数を「1」とすることで空白セルを（途中のものも含めすべて）削除しています。B3:B12の範囲はもともと縦1列なので並べ替えはされませんが、空白セルを除くためだけにTOCOL関数を利用しています。
空文字列（""）を除くことができない点はTRIMRANGE関数の例と同様です。
TAKE/FILTER関数を使う方法
無用の空白セルを除くのにFILTER関数を使う方法です。
やや式が長くなりますが、空文字列（""）を取得しないようにできるのがポイントです。
D3セル
=TAKE(FILTER(B3:B12,B3:B12<>""),-1)
「FILTER(B3:B12,B3:B12<>"")」で1文字以上入力されているセルだけを残し、TAKE関数で残ったうちの一番下のセルを取得しています。
TAKE関数のせいでMicrosoft 365でしか使えませんが、2021で利用したい場合は「=INDEX(FILTER(～),ROWS(FILTER(～)))」のようにするのが一つの方法です。
この式では、次の画像のように末尾（B11セル）に空文字列があっても無視します。つまり空文字列を取得せずに他の1文字以上の文字を取得します。
XLOOKUP関数を使う方法
XLOOKUP関数の「下から検索する」という機能を利用する方法です。
正規表現を併用する方法とワイルドカードを併用する方法を紹介します。ご自身のバージョンで利用できるものを確認の上ご利用ください。
まずは正規表現を利用する方法です。
D3セル
=XLOOKUP(".",B3:B12,B3:B12,,3,-1) 第6引数を「-1」とすることで下方から順に検索し、さらに第5引数を「3」とすることで正規表現による検索を行っています。
そして第1引数（検索値）を「"."」つまり任意の1文字とすることで結果的に「1文字以上入力されているセルのうち最も下にあるセル」の値を取得しています。
正規表現が数値にもマッチするナイス仕様なので数値も取得できます。また、空文字列を無視します。
次にワイルドカードを利用する方法です。
D3セル
=XLOOKUP("?*",B3:B12&"",B3:B12,,2,-1) 第6引数を「-1」として下方から検索し、第5引数を「2」とすることでワイルドカード検索を行っています。
第1引数（検索値）を「?*」、第2引数（検索範囲）を「B3:B12&""」としているのがちょっと面倒なところです。細かい説明は省略しますが、これは数値を取得し、空文字列を無視するための設定です。
INDEX/SUMPRODUCT関数を使う方法（旧バージョン用）
スピル機能がない古いバージョンでも実行できる方法です。
動作はExcel2010で確認していますが、画像はMicrosoft 365で実行したときのものです。
D3セル
=INDEX(B:B,SUMPRODUCT(MAX(ROW(B:B)*(B:B<>""))))
これまでの例と異なり、参照範囲が「B:B」つまりB列全体であることに注意してください。
大まかな説明ですが「SUMPRODUCT(MAX(ROW(B:B)*(B:B<>"")))」により、1文字以上入力されているセルの行番号のうち最も大きいものを取得します。
あとはINDEX関数を使い、B列中のその行番号のセルの値を取得しています。
参照している範囲がB列全体なので、B3セル以降に1つも値がない場合にB2セルの「商品」という値を取得してしまうのが注意点です。
この数式でも数値を取得でき、空文字列を無視します。
上記例と同様に「B3:B12」の範囲だけを参照するようにしたい場合はOFFSET関数を併用する方法やINDIRECT関数を併用する方法が考えられます。
次の画像はINDIRECT関数を併用する例です。
D3セル
=INDIRECT("B"&SUMPRODUCT(MAX(ROW(B3:B12)*(B3:B12<>""))))「B」の字に、取得した行番号（ここでは10）をくっつけて参照するという方法です。
