EXCEL XLOOKUP

これをマスター

マスター VLOOKUPの進化版! XLOOKUPで条件にあったデータを探そう

テーマ

商品名で部分検索して売上を探そう
=XLOOKUP( 検索値, 検索範囲, 結果範囲, 不一致の場合, 一致モード, 検索モード )
 
 
 ABCDEFG
1
2=XLOOKUP(‘ドリップコーヒー’,D7:D15, F7:F15)
3
4
5
5連番販売日カテゴリー商品名サイズ価格
712021/01/01コーヒードリップコーヒーショート290
822021/02/01コーヒードリップコーヒートール330
932021/03/01コーヒードリップコーヒーグランデ370
1042021/04/01コーヒードリップコーヒーベンティ410
1152021/01/01コーヒープレミアムショート650
1262021/02/01コーヒープレミアムトール680
1372021/03/01コーヒープレミアムグランデ700
1482021/04/01コーヒープレミアムベンティ740
1592021/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つのパラメターだけで使うケースが多いです

もう一歩

今度はちょっと複雑な例をみてみましょう

  • 商品名に「ドリップ」を含む行 (ドリップもドリップコーヒーも対象)
  • 検索文字をセルで指定して
  • データの下向きから探して
  • 商品名、サイズ、価格を表示しましょう
 
 
 ABCDEFG
1ドリップ*
2=XLOOKUP(B1,D8:D16,D8:F16,,2,-1)
3結果ドリップコーヒーベンティ410
4
5
5連番販売日カテゴリー商品名サイズ価格
712021/01/01コーヒードリップコーヒーショート290
822021/02/01コーヒードリップコーヒートール330
932021/03/01コーヒードリップコーヒーグランデ370
1042021/04/01コーヒードリップコーヒーベンティ410
1152021/01/01コーヒープレミアムショート650
1262021/02/01コーヒープレミアムトール680
1372021/03/01コーヒープレミアムグランデ700
1482021/04/01コーヒープレミアムベンティ740
1592021/04/02アイスコーヒーナイトロベンティ460

=XLOOKUP(B1,D8:D16,D8:F16,,2,-1)

検索値: B1セルで検索値を指定しています
検索範囲:「ドリップ*」アスタリスクを付ける事で、それ以降の文字列はなんでも含めるとしています
こうする事で「ドリップ」でも「ドリップコーヒー」「ドリップ豆」でも検索対象になります
結果範囲: D8:F16と複数指定する事で3列の結果が返ってきます
一致モード: 2とする事で部分一致を指定します
検索モード: -1とする事で下から上に検索しています

結果は、スペース都合上3行目に表示しています
ドリップコーヒー ベンティ 410 が
B、C、D列に表示されます

あわせてご覧ください

EXCEL SUMIF

Excel

Excel SUMIFの使い方を分かりやすい例で紹介

Read More