縦横スピルしない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をしっかり覚えて使えるようになることをお勧めします。
同じテーマ「エクセル入門」の記事
TEXTBEFORE関数（テキストの指定文字列より前の部分を返す）
TEXTAFTER関数（テキストの指定文字列より後ろの部分を返す）
LAMBDA以降の新関数の使用例
縦横スピルしないXLOOKUP代替（MATCH+INDEX,FILTER,CHOOSEROWS）
直積（クロス結合、交差結合）とピボット解除
複数の文字列を検索して置換するSUBSTITUTE
LAMBDA以降の新関数の問題集
LAMBDA以降の新関数の問題と解説（ヘルパー関数編）
LAMBDA以降の新関数の問題と解説（配列操作関数編）
PY関数（Pythonコードをセル内で実行）
GROUPBY関数（縦軸でグループ化して集計）
新着記事NEW ・・・新着記事一覧を見る
TRIMRANGE関数（セル範囲をトリム：端の空白セルを除外）｜エクセル入門(2024-08-30)
正規表現関数（REGEXTEST,REGEXREPLACE,REGEXEXTRACT）｜エクセル入門(2024-07-02)
エクセルが起動しない、Excelが立ち上がらない｜エクセル雑感(2024-04-11)
ブール型（Boolean）のis変数・フラグについて｜VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する｜VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次｜エクセルの神髄(2024-03-20)
VBA10大躓きポイント（初心者が躓きやすいポイント）｜VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成｜ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。｜エクセル雑感(2024-02-17)
初級脱出10問パック｜VBA練習問題(2024-01-24)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.RangeとCellsの使い方｜VBA入門
5.繰り返し処理（For Next)｜VBA入門
6.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
7.メッセージボックス(MsgBox関数)｜VBA入門
8.セルのクリア（Clear,ClearContents）｜VBA入門
9.条件分岐（Select Case）｜VBA入門
10.ブック・シートの選択（Select,Activate）｜VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。