ExcelのXLOOKUP関数で連続しない列から値を取り出すワザ ～取り出し順の変更も可能

連続しない列も、1つの数式で取り出せる！

　2025年の10月にExcel 2016 / 2019の延長サポートが終了します。そろそろ最新のExcelへの移行を検討している人もいるでしょう。最新バージョンのExcelでは、VLOOKUP関数に代わる“XLOOKUP関数”が利用できる点は見逃せません。

　XLOOKUP関数の基本的な使い方は以前の記事で紹介していますが、VLOOKUP関数よりも洗練されて、より手軽に使えるようになりました。

　これまでVLOOKUPを使っていた人は、さらに捗ると思います。例えば、以下のような表で、会員IDから氏名を取り出す場合は、3つの引数だけで処理できます。

XLOOKUP関数を使って、会員IDから氏名を取り出している例。3つの引数［検索値］［検索範囲］［戻り範囲］だけで取得可能

　しかし、残りのフィールド「受講履歴」「会員種別」「健康スコア」を取り出す時に困ります。参照元の表では、左から「氏名」「会員種別」「受講履歴」「健康スコア」と順番も異なり、連続もしていません。

　VLOOKUP関数なら数式をコピーして、引数［列番号］の数値を変更する以外の選択肢はありませんが、XLOOKUP関数でも「=XLOOKUP(Q2,A2:A101,L2:L101)」のような数式を1つずつ入力するしかないのでしょうか？　今回は、XLOOKUP関数をもっと活用するための小ワザを紹介したいと思います。

HSTACK関数で参照する表を整える

　XLOOKUP関数の特徴は、検索値を検索するセル範囲（検索範囲）と該当する値を返すセル範囲（戻り範囲）を別々に指定できて、さらにスピルによって連続するセルの値を1つの数式で取得できることにあります。

　要するに、引数［戻り範囲］に指定するセル範囲を整えてしまえばいいわけです。そんな都合の良い関数が“HSTACK”です。

HSTACK関数の構文。引数［配列］に指定したセル範囲を横方向に重ねて表示する

　先ほどの表を元に「氏名」「受講履歴」「会員種別」「健康スコア」の順に組み合わせてみましょう。引数［配列］にはセル範囲を指定するだけです。「,」で区切って、意図する順番に指定します。

HSTACK関数の引数として、①「氏名」、②「受講履歴」、③「会員種別」、④「健康スコア」の順にセル範囲を指定する

HSTACK関数の動作を確認するために、セルR4に「=HSTACK(B2:B101,L2:L101,F2:F101,O2:O101)」と入力した

左から順に「氏名」「受講履歴」「会員種別」「健康スコア」の値が取り出せた

XLOOKUP関数に組み込む

　セルR4に入力した数式で「氏名」「受講履歴」「会員種別」「健康スコア」の値が取り出せることがわかったので、XLOOKUP関数と組み合わせます。 セルR2に入力してあるXLOOKUP関数の数式を修正します。結果はスピルで取り出されるので、1つの数式を入力するだけで済みます。

セルR4に入力したHSTACK関数の数式の「=」より後ろをコピーする

セルR2の数式の「B2:B101」の部分を削除して、コピーした数式を貼り付ける。「=XLOOKUP(Q2,A2:A101,HSTACK(B2:B101,L2:L101,F2:F101,O2:O101))」となる

会員IDに該当する「氏名」「受講履歴」「会員種別」「健康スコア」の値がスピルで取り出せた