これをマスター
マスター VLOOKUPの進化版! XLOOKUPで条件にあったデータを探そうテーマ
| A | B | C | D | E | F | G | |
| 1 | |||||||
| 2 | =XLOOKUP(‘ドリップコーヒー’,D7:D15, F7:F15) | ||||||
| 3 | |||||||
| 4 | |||||||
| 5 | |||||||
| 5 | 連番 | 販売日 | カテゴリー | 商品名 | サイズ | 価格 | |
| 7 | 1 | 2021/01/01 | コーヒー | ドリップコーヒー | ショート | 290 | |
| 8 | 2 | 2021/02/01 | コーヒー | ドリップコーヒー | トール | 330 | |
| 9 | 3 | 2021/03/01 | コーヒー | ドリップコーヒー | グランデ | 370 | |
| 10 | 4 | 2021/04/01 | コーヒー | ドリップコーヒー | ベンティ | 410 | |
| 11 | 5 | 2021/01/01 | コーヒー | プレミアム | ショート | 650 | |
| 12 | 6 | 2021/02/01 | コーヒー | プレミアム | トール | 680 | |
| 13 | 7 | 2021/03/01 | コーヒー | プレミアム | グランデ | 700 | |
| 14 | 8 | 2021/04/01 | コーヒー | プレミアム | ベンティ | 740 | |
| 15 | 9 | 2021/04/02 | アイスコーヒー | ナイトロ | ベンティ | 460 | |
はじめに
XLOOKUPは、長年使われてきたVLOOKUPやHLOOKUPの進化版です
( VLOOKUPもあわせてご参照頂くとしっくりきます)
上の例では
D列の商品名から「ドリップコーヒー」を探して
値段を表示しています
VLOOKUP HLOOKUPから大きく3点が改善されました
- 一番左に検索範囲を指定しなくてよい
- 部分検索が出来るようになった
- 複数のセルに結果を出せる
一番左に検索範囲を指定しなくてよい
VLOOKUPでは、探したいキーワードが
必ず範囲指定の一番左の列になってないとだめでした
XLOOKUPでは、検索範囲と結果範囲が別々に指定出来るのでこのしばりが無くなりました
部分検索が出来るようになった
部分検索とは、文字の一部があってる物を探す事です
例えば「ドリップコーヒー」でも「ドリップ」のどちらも見つけたい場合
“ドリップ*” という風に、検索文字に* (アスタリスク)をつける事で
その文字以降はどんな文字でも検索してね、と言う意味になります
複数のセルに結果を出せる>
VLOOKUPでは一つを一つのセルにしか返せなかったですが
XLOOKUPでは複数の値を複数のセルに返す事ができます
これによって、VLOOKUPを何回も並べて書く必要がなくまります
詳しく
=XLOOKUP（ 検索値, 検索範囲, 結果範囲, 不一致の場合, 一致モード, 検索モード ）
検索値には、検索したい値、セルを指定します
検索範囲には、検索したい範囲を指定します
結果範囲には、範囲の結果を返すかを指定します
VLOOKUPと違いは複数指定可な点です
不一致の場合には、見つからなかった場合に何を返すかを指定します
一致モードには、0 (完全一致) 、-1 (完全一致もしくは小さい値) 、1 (完全一致もしくは大きい値) 、2 (部分一致)をしてします
指定しない場合は、0が使われます
検索モードには、1 (上から下に検索)、-1 (下から上に検索)、2 (バイナリ検索順方向)、-2 (バイナリ検索逆方向)を指定します
指定しない場合は、1が使われます
特に最後の二つのパラメターは理解が難しいかもですが
省略した場合は、デフォルトの値が使われますので
最初の例のように最初の3つのパラメターだけで使うケースが多いです
もう一歩
今度はちょっと複雑な例をみてみましょう
- 商品名に「ドリップ」を含む行 (ドリップもドリップコーヒーも対象)
- 検索文字をセルで指定して
- データの下向きから探して
- 商品名、サイズ、価格を表示しましょう
| A | B | C | D | E | F | G | |
| 1 | ドリップ* | ||||||
| 2 | =XLOOKUP(B1,D8:D16,D8:F16,,2,-1) | ||||||
| 3 | 結果 | ドリップコーヒー | ベンティ | 410 | |||
| 4 | |||||||
| 5 | |||||||
| 5 | 連番 | 販売日 | カテゴリー | 商品名 | サイズ | 価格 | |
| 7 | 1 | 2021/01/01 | コーヒー | ドリップコーヒー | ショート | 290 | |
| 8 | 2 | 2021/02/01 | コーヒー | ドリップコーヒー | トール | 330 | |
| 9 | 3 | 2021/03/01 | コーヒー | ドリップコーヒー | グランデ | 370 | |
| 10 | 4 | 2021/04/01 | コーヒー | ドリップコーヒー | ベンティ | 410 | |
| 11 | 5 | 2021/01/01 | コーヒー | プレミアム | ショート | 650 | |
| 12 | 6 | 2021/02/01 | コーヒー | プレミアム | トール | 680 | |
| 13 | 7 | 2021/03/01 | コーヒー | プレミアム | グランデ | 700 | |
| 14 | 8 | 2021/04/01 | コーヒー | プレミアム | ベンティ | 740 | |
| 15 | 9 | 2021/04/02 | アイスコーヒー | ナイトロ | ベンティ | 460 | |
=XLOOKUP(B1,D8:D16,D8:F16,,2,-1)
検索値: B1セルで検索値を指定しています
検索範囲:「ドリップ*」アスタリスクを付ける事で、それ以降の文字列はなんでも含めるとしています
こうする事で「ドリップ」でも「ドリップコーヒー」「ドリップ豆」でも検索対象になります
結果範囲: D8:F16と複数指定する事で3列の結果が返ってきます
一致モード: 2とする事で部分一致を指定します
検索モード: -1とする事で下から上に検索しています
結果は、スペース都合上3行目に表示しています
ドリップコーヒー ベンティ 410 が
B、C、D列に表示されます
