縦横スピルしないXLOOKUP代替（MATCH+INDEX,FILTER,CHOOSEROWS）
LAMBDA以降の新関数を使う事を前提にしたエクセル問題をツイッターで出題しました。
そのまとめ第一弾は以下になります。
LAMBDA以降の新関数の使用例
本ページは、一連の新関数を意識したエクセル問題としては最後にするつもりで出した問題のまとめです。
出題のツイート
みんな期待のXLOOKUPは縦横同時にスピルさせられません。
そこで問題です。
2枚目のように縦横スピルする数式を作成してください。
※データは見た通り単純な検索です。
これは普通の問題です。
新関数を使っても良いですけど、、、
まあ定番の方法がありますよね。
とはいえ、最新の関数を使っても良いのですよ。
ここ数日の他の問題の数式から使えそうな関数を探して使ってみるのも良いと思います。
解答ツイート
INDEX+MATCH
XLOOKUPと同じように戻り範囲をそのまま取得します。
=INDEX(B:D,MATCH(F2:F4,A:A,0),SEQUENCE(1,COLUMNS(B:D)))
=INDEX(B2:D16,MATCH(F2:F4,A2:A16,0),MATCH(K1:M1,B1:D1,0))
=INDEX(B:D,MATCH(F2:F4,A:A,0),SEQUENCE(1,COLUMNS(B:D)))
説明不足で理解してもらえていないかもしれないですね。
というか、あまり読まれてもいないのかwww
XLOOKUPの書き換えなら、
=INDEX(B:D,MATCH(F2,A:A,0),0)
これで良い事になります。
この違いを説明しなかった・・・
=INDEX(B:D,MATCH(F2:F4,A:A,0),0)
このように指定した場合は、縦にスピルしません。
これはXLOOKUPが縦横同時スピルしない事と同じです。
そこで前記の数式ではSEQUENCEを使い横配列で取得するようにしています。
↓
正：縦にしかスピルしません。
FILTER+MATCH
=FILTER(B:D,NOT(ISNA(XMATCH(A:A,F2:F4))))
NOT(ISNA(は、ISNUMBER( でも良いです。
FILTER関数では、元表と同じ行数のTRUE/FALSEの配列が必要になります。
そうなると元表のA列の方からF列を検索することになってしまいます。
そこで前記の数式は行数の範囲を限定した数式にしました。
（すみません、画像とツイートの数式が違ってしまいました。）
また、A列にない名前をF列に入れるとFILTERで抜け落ちるのでその行分が詰まってしまいます。
あまり使い勝手が良くないです。
CHOOSEROWS+MATCH
CHOOSEROWS関数が使えそうです。
抽出する行番号を配列で指定します。
=CHOOSEROWS(B:D,XMATCH(F2:F4,A:A))
データ件数を増やしてランダム順で取得してみましたがCHOOSEROWSはとても速いようです。
時間がかかるのはMATCHの方で、これは仕方ないですね。
数式全体がエラーになってしまいます。
これは都合が悪いですね。
そこでMATCHをIFERRORでくるみます。
でも、エラーの時に何を入れたら良いのか…
row_numなので数値しか入れられません。
こんな時はとりあえず 0 かなとw
これは上手くいきました、検索値が無い行を抜かしてきちんと取得されました。
（分かり易いように名前も取得しました）
しかしFILTER同様に行が詰まるのは、場合によっては都合が悪い場合も出てきそうです。
MATCHのエラー時の 0 を -1 にしてみたらどうなるか…
=CHOOSEROWS(A:D,IFERROR(XMATCH(F2:F4,A:A),-1))
0が出てきました、なんとなく上手く行っちゃいましたね。
でも、この 0 はどこからでてきたのでしょう…
はい、想像通りに(笑) -1 は最終行でした。
-2,-3と下から上に取得できるようです。
以下のようになっているようです。
1 ～ 最大行数：上からの行位置
-1～ -最大行数：下からの行位置
0 もしくは 最大行数超：その行は無視される
0 および 最大数超 の場合は見当たりませんでした…
しかし、これは完全に意図して作成された仕様と思えるので、たぶん今後もこの仕様のままでいくだろうと思います。
後日追加
row_numまたはcol_numの最後の要素に、0 または最大数を超える値を指定した場合は「#VALUE!」になってしまいます。
上記の例のように途中の 0 は無視して詰められますが、最後が 0 の場合はエラーとなります。
先頭の要素では問題ありませんので、なぜ最後だけエラーになるのかは謎仕様です。
無理矢理XLOOKUPを使ってみたら…
=LET(
f,F2:F4,
s,A:A,
r,B:D,
c,LAMBDA(x,y,CHOOSECOLS(TEXTSPLIT(x,","),y)*1),
m,MAKEARRAY(ROWS(f),COLUMNS(r),LAMBDA(x,y,x&","&y)),
MAP(m,LAMBDA(m,XLOOKUP(CHOOSEROWS(f,c(m,1)),s,CHOOSECOLS(r,c(m,2)))))
)
素直にINDEX+MATCHを使えよという事例でしたw
何と言ってもINDEX+MATCHの使い勝手が良くて優秀で、それと比べてしまうと…
まあXLOOKUPは少ない引数で簡単に使えるというのが良さですね。
総評
・FILTERは少ない条件で表を絞り込むなら。
・CHOOSEROWSは別途行番号リストを取得して表の絞り込みには。（SQLならIN句での行番号絞り込み）
使い分けできれば良いですが、まずはINDEX+MATCHをしっかり覚えて使えるようになることをお勧めします。
ワークシート関数全一覧（最新版）
ワークシート関数一覧（2007まで）
ワークシート関数一覧（2010以降）
同じテーマ「エクセル入門」の記事
TEXTBEFORE関数（テキストの指定文字列より前の部分を返す）
TEXTAFTER関数（テキストの指定文字列より後ろの部分を返す）
LAMBDA以降の新関数の使用例
縦横スピルしないXLOOKUP代替（MATCH+INDEX,FILTER,CHOOSEROWS）
直積（クロス結合、交差結合）とピボット解除
複数の文字列を検索して置換するSUBSTITUTE
LAMBDA以降の新関数の問題集
LAMBDA以降の新関数の問題と解説（ヘルパー関数編）
LAMBDA以降の新関数の問題と解説（配列操作関数編）
PY関数（Pythonコードをセル内で実行）
GROUPBY関数（縦軸でグループ化して集計）
新着記事NEW ・・・新着記事一覧を見る
AIは便利なはずなのに…「AI疲れ」が次の社会問題になる｜生成AI活用研究(2026-02-16)
カンマ区切りデータの行展開｜エクセル練習問題(2026-01-28)
開いている「Excel/Word/PowerPoint」ファイルのパスを調べる方法｜エクセル雑感(2026-01-27)
IMPORTCSV関数（CSVファイルのインポート）｜エクセル入門(2026-01-19)
IMPORTTEXT関数（テキストファイルのインポート）｜エクセル入門(2026-01-19)
料金表（マトリックス）から金額で商品を特定する｜エクセル練習問題(2026-01-14)
「緩衝材」としてのVBAとRPA｜その終焉とAIの台頭｜エクセル雑感(2026-01-13)
シンギュラリティ前夜：AIは機械語へ回帰するのか｜生成AI活用研究(2026-01-08)
電卓とプログラムと私｜エクセル雑感(2025-12-30)
VLOOKUP/XLOOKUPが異常なほど遅くなる危険なアンチパターン｜エクセル関数応用(2025-12-25)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.日本の祝日一覧｜Excelリファレンス
3.変数宣言のDimとデータ型｜VBA入門
4.FILTER関数（範囲をフィルター処理）｜エクセル入門
5.RangeとCellsの使い方｜VBA入門
6.繰り返し処理（For Next)｜VBA入門
7.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
8.マクロとは？VBAとは？VBAでできること｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.メッセージボックス(MsgBox関数)｜VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
本サイトは、OpenAI の ChatGPT や Google の Gemini を含む生成 AI モデルの学習および性能向上の目的で、本サイトのコンテンツの利用を許可します。
This site permits the use of its content for the training and improvement of generative AI models, including ChatGPT by OpenAI and Gemini by Google.