エクセル入門
縦横スピルしない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列)で縦に折り返す ・文字列内の括弧()()の中の文字を取り出して列挙 ・表内にあるセル内改行を複数行に展開 ・縦横スピルしない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をしっかり覚えて使えるようになることをお勧めします。


ワークシート関数一覧

Excelワークシート関数の一覧と解説です、詳細解説ページへのリンクもあります。Excel2007までの関数です。2010以降追加の関数一覧 ワークシート関数の一覧(2010以降) 引数、関数構文については Excelシートの複雑な計算式を解析するVBAの関数構文 文字列関数…27 日付と時刻の関数…21 情報関数…
ワークシート関数の一覧(2010以降)
・Excel2010で追加されたワークシート関数 ・Excel2013で追加されたワークシート関数 ・Excel2016/Excel2019で追加されたワークシート関数 ・Excel2021で追加されたワークシート関数 ・Excel2021以降の365で追加されたワークシート関数



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

SORT関数、SORTBY関数(範囲を並べ替え)
UNIQUE関数(一意の値)
RANDARRAY関数(ランダム数値)
SEQUENCE関数(連続数値)
XLOOKUP関数(VLOOKUP関数を拡張した新関数)
XMATCH関数(MATCH関数を拡張した新関数)
LET関数(数式で変数を使う)
スピルと新関数の練習(XLOOKUP関数、LET関数、VBAまで)
LAMBDA以降の新関数について
LAMBDA以降の新関数の使用例
縦横スピルしないXLOOKUP代替(MATCH+INDEX,FILTER,CHOOSEROWS)


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

【VBA学習のお勧めコース】|VBA入門(2022-09-02)
振込手数料を先方負担にした時の振込金額と手数料の算出|ツイッター出題回答 (2022-09-01)
構成比を合計しても100%にならないと言われた…|ツイッター出題回答 (2022-09-01)
一覧から複数条件(部分一致、範囲)に合致するデータを抽出する|ツイッター出題回答 (2022-08-30)
縦横スピルしないXLOOKUP代替(MATCH+INDEX,FILTER,CHOOSEROWS)|エクセル入門(2022-08-27)
IF関数の論理式で比較演算子を省略したCOUNT系関数を書くのは|ツイッター出題回答 (2022-08-23)
LAMBDA以降の新関数の使用例|エクセル入門(2022-08-22)
数珠順列(配置に条件付き)を全て出力する|ツイッター出題回答 (2022-08-20)
日付時刻のマイナス表示に対応する方法|ツイッター出題回答 (2022-08-17)
LAMBDA以降の新関数について|エクセル入門(2022-08-16)


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

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




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


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



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