こんにちは。
TECH BLOGの萱場です。
今回は、表題の通りVLOOKUP関数の代りに、INDEX関数とMATCH関数を使用する解説を行いたいと思います。
VLOOKUPの弱点
VLOOKUP関数を使用されている方は、ご存知かもしれませんが、VLOOKUP関数の弱点は、【検索キーより右側の値しか抽出できない】ことです。
上記の表の場合、VLOOKUPでは、品番列から、サイズや色、価格の値を取ることはできますが、商品IDから、サイズや色、価格を取ることはできません。
XLOOKUP
元も子もない話ですが、最新のエクセルやスプシでは、XLOOKUPという関数を使って、上記の表の商品IDを検索キーにサイズなどの検索キーより右側の値を取ることができます。
XLOOKUP(検索キー, 検索範囲, 結果の範囲, 見つからない場合の値, 一致モード, 検索モード)
https://support.google.com/docs/answer/12405947?hl=ja#zippy=
解説
例えばI3に商品IDの単価を持ってきたいとき、以下の様に指定すると単価が取れます。
XLOOKUP(H3,$F$3:$F$5,$E$3:$E$5,”一致なし”,0)
最初の引数で、検索する商品IDを指定します。
次の引数では、その商品IDを検索する範囲を一次配列で指定します。
3番目の引数で、マッチした場合に取りたい値の範囲を選択します。(今回の場合は価格の範囲)
4番目の引数は、検索に一致しない場合に出力する文字列を、設定します。
5番目の引数は、一致モードです。完全一致の場合は0を使用します。(省略可)
今回は、すでに省略してしまっていますが、6番目の検索方法があります。
それは、検索モードの設定で、線形探索と二分探索を選ぶことができます。(デフォルトは、上から順に線形探索です。二分探索の場合は、昇順、降順に並び替えたうえで関数を使用する必要があります)
値を取ることができました。
XLOOKUP関数の便利なところは、右から左に検索できることもそうですが、VLOOKUP関数のときに不便だった指数の指定が必要ありません。
検索結果で使いたい範囲を直接指定するので、わかりやすいですね。
INDEX & MATCH関数
XLOOKUP関数が使える状況では、このINDEX&MATCH関数を使用する必要性はありませんが、古いエクセルなどだと、使えない場合があり、そんなときでも使用できるのが、今回の組み合わせ関数です。
INDEX関数
INDEX(参照, [行], [列])
https://support.google.com/docs/answer/3098242?hl=ja&sjid=1053379139656346441-AP
インデックス関数は、指定範囲の中から、第一引数(行)、第二引数(列)に該当する値を返すというものです。
公式ドキュメントを見てもらうとよりわかりやすいと思います。
MATCH関数
MATCH(検索キー, 範囲, 検索の種類)
https://support.google.com/docs/answer/3093378?hl=ja&sjid=1053379139656346441-AP
第二引き数の検索範囲より、第一引数の検索キーがある相対的な位置を返します。
検索の種類は、0が完全一致になります。
INDEX関数とMATCH関数を合体させると、、、
流れから考えると、
- INDEX関数の参照範囲に、値を取りたい範囲を指定する(今回の場合は、価格の部分)
- INDEX関数の第二引数(行)に、MATCH関数を入力する。
- MATCH関数の第1引数に、価格を知りたい商品の商品IDを指定する。第2引数の範囲に表の商品IDの範囲を指定する。
今回の場合、商品ID【100001】からその商品の価格を知りたいとします。そうすると
- MATCH関数では、100001の商品IDの相対的な位置を取得。→商品IDの一番上にあるので“1”が返ってくる。
- INDEX関数では、E3からE5までの範囲を参照範囲として指定しているので、その中の1行目の”1000″という値が返ってくる。
こんな感じです。
まとめ
最後まで、お読みいただきありがとうございました。
INDEX関数は、XLOOKUPの代わり以外にもクロス集計をしたりする際に使用できます。
慣れた関数を使っているともっと便利な関数があっても、古い関数を使ったままになり、解決方法や思考の枠が狭まってしまうこともあるので、注意が必要ですね。
株式会社デザインXでは、アパレル業界のお客様に向けて、ECサイトや業務基幹システム(ERP)の開発・導入支援を行っております。ご興味をお持ちの方は、是非下記リンクよりお気軽にお問い合わせください。
