【Excel】XLOOKUPで列番号を指定できないときの代わりの書き方｜クロス表はXLOOKUPを2回使う

XLOOKUPに乗り換えてから、VLOOKUPと同じ書き方ができずに困ったことがあります。商品と店舗の2つの条件で、クロス表から値を取り出すときです。

私は仕事で、縦に約1000の商品、横に十数店舗が並んだ在庫数の表を使っています。別のシートで、ある商品のある店舗の在庫数を調べるためです。

VLOOKUPを使っていたときは、MATCH関数で店舗が何列目かを求めたり、店舗が店舗コード順に並んでいるので、店舗コードから列番号を判定したりしていました。どちらも、VLOOKUPの「何列目を返すか」を指定する引数を使う方法です。

ところがXLOOKUPには、この列番号を指定する引数がありません。代わりの方法をAIに聞いたところ、XLOOKUPを2回使う書き方を教えてもらいました。今はこの方法で在庫数を取り出しています。

この記事では、VLOOKUPでやっていたことをXLOOKUPでどう書けばよいかを紹介します。なお、VLOOKUP自体は今も使えます。XLOOKUPに統一したい場合の書き方です。

例として使うクロス表

「在庫表」シートに、商品ごと・店舗ごとの在庫数があるとします。

店舗名はB1:D1、商品名はA2:A5、在庫数はB2:D5に入っています。

別のシートのA2で商品、B2で店舗をプルダウンから選び、C2にその在庫数を取り出します。プルダウンにしておくと、入力ミスで「#N/A」になることを防げます。作り方は別の記事で紹介します。

VLOOKUPでは、3つ目の引数で「範囲の左から何列目を返すか」を指定します。店舗によって列が変わるので、MATCHで店舗が何列目かを求めて渡していました。

=VLOOKUP(A2,在庫表!A2:D5,MATCH(B2,在庫表!A1:D1,0),FALSE)

MATCHの範囲をA1:D1にしているのは、VLOOKUPの列番号がA列から数えるためです。店舗が店舗コード順に並んでいる場合は、店舗コードから列番号を求めることもできます。どちらも、「何列目か」という数字で列を指定しています。

XLOOKUPは、探す範囲と返す範囲をそれぞれ指定する関数です。

=XLOOKUP(検索値,検索範囲,戻り範囲)

「何列目」を指定する引数がないので、VLOOKUPとMATCHの式をそのままXLOOKUPに置き換えることはできません。

XLOOKUPでは、次のように書きます。

=XLOOKUP(A2,在庫表!A2:A5,XLOOKUP(B2,在庫表!B1:D1,在庫表!B2:D5))

内側と外側に分けると、やっていることは次のとおりです。

例えば、A2が「商品C」、B2が「駅前店」なら、結果は「21」です。

VLOOKUPでは「何列目か」を数字で渡していましたが、XLOOKUPでは内側のXLOOKUPで店舗の列そのものを取り出します。これが列番号の代わりになります。

VLOOKUPとMATCHの式のどこが、XLOOKUPを使う式のどこに当たるかをまとめました。

内側のXLOOKUPでは、店舗名の範囲（B1:D1）と在庫数の範囲（B2:D5）の列をそろえます。列がそろっていないと、「#VALUE!」になります。

クロス表から値を取り出す方法は、INDEXとMATCHを組み合わせる方法もあります。

=INDEX(在庫表!B2:D5,MATCH(A2,在庫表!A2:A5,0),MATCH(B2,在庫表!B1:D1,0))

私も使ったことがありますが、INDEXの中にMATCHを2回入れるので式が長くなり、初心者には難しいと感じました。XLOOKUPを2回使う方法は、いつものXLOOKUPの形をくり返すだけなので、今はこちらを使っています。

XLOOKUPに乗り換えて便利だったことは、こちらの記事で紹介しています。