Excel。値を検索して、複数の列から対応する結果を抽出したい
＜VLOOKUP関数＋IF関数・XLOOKUP＋INDIRECT関数＞
会員と一般で、単価が異なる表から、会員なら会員の、一般なら一般の単価を抽出し表示したい場合、どのようにしたらいいのでしょうか。
次の表をつかって説明します。
A2の所属が一般で、商品コードがB2のA01です。A5:D7の表から、商品コードがA01の
一般なので、D2の単価は、100と表引きされています。
A2の値を会員にすれば、D2の単価は80となるようにしたいわけです。
このような場合、VLOOKUP関数をつかうといいように思えますが、うまくいきません。
C2の商品名は、VLOOKUP関数で対応することは、できます。
C2の数式は、
=VLOOKUP(B2,A6:D7,2,FALSE)
VLOOKUP関数の最初の引数、検索値は、B2
2つ目の引数は、範囲なので、A6:D7 と設定します。
3つ目の引数は、列番号です。
2つ目の引数で設定した範囲の左から何列目のデータを表示するのかということなので、左から「2」列目なので、2と設定します。
最後の引数の、検索方法は、完全一致ですから、FALSE。
商品名は、VLOOKUP関数でもいいのですが、問題は、列番号です。
会員ならば、3。一般なら4としなければなりません。
このような場合、
=VLOOKUP(B2,A6:D7,IF(A2="会員",3,4),FALSE)
と列番号を判断させるようにIF関数をつかってもいいと思います。
ただし、所属数が会員・準会員・一般・学生のように、増えた場合、IF関数では対応するのが大変になるので、多分岐できる関数を使う必要がでてきます。
また、所属、それぞれの表をつくって、INDIRECT関数をつかって対応する方法があります。
ただ、その方法では、別表を作る必要がありますので、面倒です。
そこで、INDIRECT関数はつかうのですが、VLOOKUP関数よりもXLOOKUP関数で対応する方法があります。
事前に「名前の定義」を設定します。
C6:C7に、「会員」
D6:D7に、「一般」
と名前を設定しました。
D2の数式は、
=XLOOKUP(B2,A6:A7,INDIRECT(A2),"",0,1)
A2の所属を変更しても、会員と一般、それぞれの数値を表引きしてくれます。
もし、所属数が増えても、名前の定義を増やすだけで、数式を変更する必要はありません。
では、
=XLOOKUP(B2,A6:A7,INDIRECT(A2),"",0,1)
XLOOKUP関数の数式を確認します。
最初の引数は、検索値 なので、B2を設定します。
2つ目の引数は、検索範囲。
検索値が所属されている範囲なので、A6:A7。
3つ目の引数は、戻り範囲。
表示したい範囲です。
ここに、会員なのか、一般なのかで、戻り範囲を対応させたい。
そこで、INDIRECT関数をつかうことで、戻り範囲を変更させています。
INDIRECT関数は、値そのものをつかうことができます。
名前の定義で、会員と一般を設定していますので、切り替えることができるというわけです。
4つ目の引数は、見つからない場合。
見つからない時は「””（ダブルコーテーション×2）」で空白と設定します。
5つ目の引数は、一致モードなので、０の完全一致にしました。
6つ目の引数は、検索モード。
先頭から検索させますので、1と設定します。
このように、XLOOKUP関数とINDIRECT関数を組み合わせることで、値を検索して、複数の列から対応する結果を抽出することができます。