エクセル入門
縦横スピルしないXLOOKUP代替(MATCH+INDEX,FILTER,CHOOSEROWS)

Excelの初心者向け入門解説
最終更新日:2022-09-04

縦横スピルしないXLOOKUP代替(MATCH+INDEX,FILTER,CHOOSEROWS)


LAMBDA以降の新関数を使う事を前提にしたエクセル問題をツイッターで出題しました。
そのまとめ第一弾は以下になります。
LAMBDA以降の新関数の使用例

・2つの1次元配列から、それをクロス結合した結果を返す ・A列が同じ行のB列の値を連結して、A列の一意な値とともに出力 ・名前定義を使わずにLAMBDA関数で再帰する方法 ・文字列を大文字小文字変換して大小文字の全組み合わせを出力 ・2つのテーブルの片方しかない行を縦に連結出力 ・3連単、3連複のフォーメーション買目を全て列挙する ・FILTER関数の出力行数・列数を指定する ・月が縦で日が横の表を、曜日(7列)で縦に折り返す ・文字列内の括弧()()の中の文字を取り出して列挙 ・表内にあるセル内改行を複数行に展開 ・文字列の中から1文字削除の全パターン出力 ・クロスABC分析 ・月利が毎月変動する月複利の計算 ・レーベンシュタイン距離 ・文字列を数字と数字以外で分割 ・縦横スピルしないXLOOKUP代替(MATCH+INDEX,FILTER,CHOOSEROWS) ・直積(クロス結合、…

本ページは、一連の新関数を意識したエクセル問題としては最後にするつもりで出した問題のまとめです。


出題のツイート

出題ツイートへのリンク

【エクセル問題】※これは覚えてほしいな編
みんな期待のXLOOKUPは縦横同時にスピルさせられません。
そこで問題です。
2枚目のように縦横スピルする数式を作成してください。
※データは見た通り単純な検索です。

エクセル Excel サンプル画像

エクセル Excel サンプル画像


他ツイートで新関数の問題は終了と言ったので特に書きませんでしたが。
これは普通の問題です。
新関数を使っても良いですけど、、、


問題としては普通の問題のつもりで出したので、まずはどんな方法でも良いのでやってみましょう。
まあ定番の方法がありますよね。
とはいえ、最新の関数を使っても良いのですよ。
ここ数日の他の問題の数式から使えそうな関数を探して使ってみるのも良いと思います。


解答ツイート

解答ツイートへのリンク

以下はツイートした内容の転載になります。


INDEX+MATCH

この事例なら、まさに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))

Excel エクセル問題 XLOOKUPは縦横にスピルしない

Excel エクセル問題 XLOOKUPは縦横にスピルしない


後日追記
最初の数式、
=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)
これで良い事になります。
この違いを説明しなかった・・・

Excel エクセル問題 XLOOKUPは縦横にスピルしない


MATCHの検索値を縦の複数行にして、
=INDEX(B:D,MATCH(F2:F4,A:A,0),0)
このように指定した場合は、縦にスピルしません。
これはXLOOKUPが縦横同時スピルしない事と同じです。
そこで前記の数式ではSEQUENCEを使い横配列で取得するようにしています。

Excel エクセル問題 XLOOKUPは縦横にスピルしない

誤:縦にスピルしません。

正:縦にしかスピルしません。


FILTER+MATCH

表から抽出する場合、FILTER関数を思い浮かべる人も多いと思います。
=FILTER(B:D,NOT(ISNA(XMATCH(A:A,F2:F4))))
NOT(ISNA(は、ISNUMBER( でも良いです。
FILTER関数では、元表と同じ行数のTRUE/FALSEの配列が必要になります。
そうなると元表のA列の方からF列を検索することになってしまいます。

Excel エクセル問題 XLOOKUPは縦横にスピルしない


データ件数が多いとかなり重くなりそうです。
そこで前記の数式は行数の範囲を限定した数式にしました。
(すみません、画像とツイートの数式が違ってしまいました。)
また、A列にない名前をF列に入れるとFILTERで抜け落ちるのでその行分が詰まってしまいます。
あまり使い勝手が良くないです。

Excel エクセル問題 XLOOKUPは縦横にスピルしない


CHOOSEROWS+MATCH

最新関数から使えそうなものは…
CHOOSEROWS関数が使えそうです。
抽出する行番号を配列で指定します。
=CHOOSEROWS(B:D,XMATCH(F2:F4,A:A))
データ件数を増やしてランダム順で取得してみましたがCHOOSEROWSはとても速いようです。
時間がかかるのはMATCHの方で、これは仕方ないですね。

Excel エクセル問題 XLOOKUPは縦横にスピルしない


では検索値が無い場合はどうなるでしょうか。
数式全体がエラーになってしまいます。
これは都合が悪いですね。
そこでMATCHをIFERRORでくるみます。
でも、エラーの時に何を入れたら良いのか…
row_numなので数値しか入れられません。
こんな時はとりあえず 0 かなとw

Excel エクセル問題 XLOOKUPは縦横にスピルしない


=CHOOSEROWS(A:D,IFERROR(XMATCH(F2:F4,A:A),0))
これは上手くいきました、検索値が無い行を抜かしてきちんと取得されました。
(分かり易いように名前も取得しました)
しかしFILTER同様に行が詰まるのは、場合によっては都合が悪い場合も出てきそうです。

Excel エクセル問題 XLOOKUPは縦横にスピルしない


検索値が無い行を何かで埋める方法はないものでしょうか…
MATCHのエラー時の 0 を -1 にしてみたらどうなるか…
=CHOOSEROWS(A:D,IFERROR(XMATCH(F2:F4,A:A),-1))
0が出てきました、なんとなく上手く行っちゃいましたね。
でも、この 0 はどこからでてきたのでしょう…

Excel エクセル問題 XLOOKUPは縦横にスピルしない


そこで、行全体指定ではなく範囲を絞って指定してみます。
はい、想像通りに(笑) -1 は最終行でした。
-2,-3と下から上に取得できるようです。
以下のようになっているようです。
1 ~ 最大行数:上からの行位置
-1~ -最大行数:下からの行位置
0 もしくは 最大行数超:その行は無視される

Excel エクセル問題 XLOOKUPは縦横にスピルしない


この仕様は「Officeのサポート」には事例の最後にマイナス値の事が出てきているだけでした。
0 および 最大数超 の場合は見当たりませんでした…
しかし、これは完全に意図して作成された仕様と思えるので、たぶん今後もこの仕様のままでいくだろうと思います。


後日追加

CHOOSEROWSおよびCHOOSECOLSでは、
row_numまたはcol_numの最後の要素に、0 または最大数を超える値を指定した場合は「#VALUE!」になってしまいます。
上記の例のように途中の 0 は無視して詰められますが、最後が 0 の場合はエラーとなります。
先頭の要素では問題ありませんので、なぜ最後だけエラーになるのかは謎仕様です。


無理矢理XLOOKUPを使ってみたら…

最後に、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)))))
)

Excel エクセル問題 XLOOKUPは縦横にスピルしない


これは何のためにXLOOKUPを使っているのか意味が分からないですね、失敗です。
素直にINDEX+MATCHを使えよという事例でしたw
何と言ってもINDEX+MATCHの使い勝手が良くて優秀で、それと比べてしまうと…
まあXLOOKUPは少ない引数で簡単に使えるというのが良さですね。


総評

・INDEX+MATCHは融通が利いて万能感がある。
・FILTERは少ない条件で表を絞り込むなら。
・CHOOSEROWSは別途行番号リストを取得して表の絞り込みには。(SQLならIN句での行番号絞り込み)
使い分けできれば良いですが、まずはINDEX+MATCHをしっかり覚えて使えるようになることをお勧めします。


ワークシート関数一覧

・文字列関数 ・日付と時刻の関数 ・情報関数 ・論理関数 ・検索/行列関数 ・数学/三角関数 ・統計関数 ・財務関数 ・エンジニアリング関数 ・データベース関数 ・アドイン/オートメーション ・キューブ ・Microsoft Office のサポート
ワークシート関数の一覧(2010以降)
・Excel2010で追加されたワークシート関数 ・Excel2013で追加されたワークシート関数 ・Excel2016/Excel2019で追加されたワークシート関数 ・Excel2021で追加されたワークシート関数 ・Excel2021より後の365で追加されたワークシート関数 ・Microsoft「Office のサポート」



同じテーマ「エクセル入門」の記事

WRAPROWS関数(1次元配列を指定数の行で折り返す)
WRAPCOLS関数(1次元配列を指定数の列で折り返す)
TEXTSPLIT関数(列と行の区切り記号で文字列を分割)
TEXTBEFORE関数(テキストの指定文字列より前の部分を返す)
TEXTAFTER関数(テキストの指定文字列より後ろの部分を返す)
LAMBDA以降の新関数の使用例
縦横スピルしないXLOOKUP代替(MATCH+INDEX,FILTER,CHOOSEROWS)
直積(クロス結合、交差結合)とピボット解除
LAMBDA以降の新関数の問題集
LAMBDA以降の新関数の問題と解説(ヘルパー関数編)
LAMBDA以降の新関数の問題と解説(配列操作関数編)


新着記事NEW ・・・新着記事一覧を見る

セル値でパス・ブック・シート名を指定|Power Query(M言語)入門(2023-02-07)
別ブックのシートを列可変で取り込む|Power Query(M言語)入門(2023-02-06)
ExcelとVBAの入門解説|エクセルの神髄(2022-12-14)
文字列のプロパティ名でオブジェクトを操作する方法|VBA技術解説(2022-12-14)
数字(1~50)を丸付き数字に変換するVBA|VBA技術解説(2022-11-15)
TEXTAFTER関数(テキストの指定文字列より後ろの部分を返す)|エクセル入門(2022-11-14)
TEXTBEFORE関数(テキストの指定文字列より前の部分を返す)|エクセル入門(2022-11-14)
TEXTSPLIT関数(列と行の区切り記号で文字列を分割)|エクセル入門(2022-11-12)
LAMBDA以降の新関数はVBAで使えるか|VBA技術解説(2022-11-11)
WRAPCOLS関数(1次元配列を指定数の列で折り返す)|エクセル入門(2022-11-08)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.繰り返し処理(For Next)|VBA入門
4.変数宣言のDimとデータ型|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.マクロって何?VBAって何?|VBA入門
7.並べ替え(Sort)|VBA入門
8.Excelショートカットキー一覧|Excelリファレンス
9.エクセルVBAでのシート指定方法|VBA技術解説
10.Range以外の指定方法(Cells,Rows,Columns)|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ