最新のExcel2021で追加された「XLOOKUP」がスゴイ!

2021/11/30 What's Up, ビジネス系 投稿者:

皆様こんにちは。ネットワーク事業部の生方です。

2021年10月に、Microsoft Officeの最新バージョンである2021が発売されました。
様々な新機能が盛り込まれたOffice 2021ですが、その中でもExcelの変更点として、新たな関数「XLOOKUP(エックスルックアップ)」が注目されています。
今回はこのXLOOKUPの機能と、これまで使われてきた関数との違いについてご紹介したいと思います。

 

XLOOKUPとは

Excelの関数をある程度ご利用の方は、「VLOOKUP」や「HLOOKUP」という関数を耳にしたり使ったりしたことがあるのではないでしょうか。
これらの関数は、あるリストの中からデータを検索して、一致する検索結果を表示したいときに使われる関数です。
VLOOKUPは、Vertical(垂直)の頭文字Vをとったもので、垂直方向にデータを検索します。一方のHLOOKUPは、Horizontal(水平)の頭文字のHで、水平方向にデータを検索します。

どちらも非常に便利な関数で、これまでも大変重宝されてきた関数ですが、ここに新たに加わったのが「XLOOKUP」です。
このXLOOKUPは、永続版のOfficeではExcel 2021から新たに使用可能となり、サブスクリプション版のMicrosoft 365でもアップデートで利用可能となっています。
残念ながら、永続版のExcel 2019までのバージョンでは使用できませんのでご注意ください。

 

XLOOKUPの書式

まずは、XLOOKUPの書式について確認しておきましょう。

=XLOOKUP( 検索値 , 検索範囲 , 戻り配列 , [見つからない場合] , [一致モード] , [検索モード])

引数 内容
検索値 ※必須 検索で使う内容
検索範囲 ※必須 検索値を探す範囲
戻り配列 ※必須 検索値が見つかった場合、結果として表示する範囲
見つからない場合 検索値が見つからなかった場合に表示する内容
一致モード 0 … 完全一致(既定)  一致しない場合は、#N/A が返されます。
-1 … 完全一致 一致しない場合は、検索値の次に小さな結果が返されます。
1 … 完全一致 一致しない場合は、検索値の次に大きな結果が返されます。
2 … ワイルドカードの一致 *や?を使用した検索で使用します。
検索モード 1 … 先頭から検索(既定)
-1 … 末尾から逆方向に検索
2 … 昇順で並べ替えられた検索範囲を使用してバイナリ検索(二分検索)。並べ替えられていない場合はエラーとなります。
-2 … 降順で並べ替えられた検索範囲を使用してバイナリ検索(二分検索)。 並べ替えられていない場合はエラーとなります。

一致モードと検索モードについては少し複雑なところなので、今回の記事では説明を省かせていただきますが、それ以外の引数だけで十分活用できます。
それでは、実際の表をもとにして使い方を見ていきましょう。

 

XLOOKUPを使ってみよう

使い方の例として、下記のような顧客リストがあったとします。

上部の検索結果で、会員番号を入力すると、自動的に対象顧客の姓・名・年齢が表示されるようにしたいと思います。

その場合に、姓を表示したいセルB3の式は以下のようになります。(※式がシンプルになるように絶対参照は省いています。)
=XLOOKUP(A3,A7:A16,B7:B16,””)

式の内容を確認していきましょう。

引数 内容
検索値 ※必須 会員番号を入力するセルA3を指定します。
検索範囲 ※必須 顧客リストから、会員番号を検索したいA7:A16を指定します。
戻り配列 ※必須 姓を表示したいので、顧客リストの姓が入っているB7:B16を指定します。
見つからない場合 未入力や結果が無いときには空欄にしたいので、式中で空欄を表す””を入力します。

いかがでしょうか?
『この数値を、この中から探して、この中から答えを出す。無いときはこれ。』
という順番で考えると、結構わかりやすい関数ではないかなと思いませんか?

 

これまでのVLOOKUP・HLOOKUPと何が違う?

今回のような表で会員番号から検索することはVLOOKUPでも可能です。しかし、XLOOKUPでとても便利になったところがあります。

結果が見つからない場合の表記が簡単になった

XLOOKUPの引数として「見つからない場合」がありますが、これはVLOOKUP・HLOOKUPにはないものでした。
例えば、今回のXLOOKUPと同じようなものをVLOOKUPで作ると、以下のような式になります。

(※セルB3に姓を表示する場合) =IF(A3=””,””,VLOOKUP(A3,A7:D16,2,FALSE))

 

IF関数と組み合わせていますが、これは「会員番号が空欄の場合は、姓も空欄にする」ためになります。
VLOOKUPだけだと、会員番号を入力していない場合には姓に#N/Aと表示されてしまいます。

XLOOKUPと比べると、関数を組み合わせないといけないので複雑になってしまいます。このようなエラー処理が簡単になったのは素晴らしいところだと思います。

検索値が左端になくてもよい

VLOOKUP関数を使う場合には、検索値が選択範囲の一番左にないといけませんでした。
例えば今回の表であれば、姓を入力して検索し、会員番号を表示したい場合にはVLOOKUP関数は使えず、他の関数を複数組み合わせて表現しなければいけません。

しかしXLOOKUP関数なら、セルA3に
=XLOOKUP(B3,B7:B16,A7:A16,””)
と入力すれば姓から会員番号を表示することも可能です。
VLOOKUPよりも柔軟な検索ができるようになったというのが大きな改良点です。

縦・横どちらでも対応できる

これまでは垂直方向の検索ではVLOOKUP、水平方向ではHLOOKUPと使い分ける必要がありましたが、XLOOKUPでは一つで両方に対応できます。
検索範囲と戻り配列の指定が縦方向なのか横方向なのかで使い分けができますので、これも素晴らしいポイントだと思います。

 

おわりに

新たな関数「XLOOKUP」についてご紹介いたしましたがいかがでしたでしょうか?
これまでもVLOOKUP・HLOOKUPは便利な関数として知られていましたが、使い方にコツがあって初心者泣かせな関数ともいわれてきました。
ですが、つまづきやすかったところが改善されて非常に使いやすくなり、さらに両方を合体さたハイブリッドな関数がXLOOKUPと言えるのではないかと思います。

唯一の欠点としては、Excel 2019以前のバージョンでは使用できません。使用するためにはExcel 2021か、Microsoft 365の利用が必要となります。
新しいOfficeでは様々な機能が追加されたり改善されたりしていますので、これを機に古いOfficeをご利用の方はアップデートを検討されてはいかがでしょうか。
弊社ではOfficeのアップデートやPCのリプレイスもサポートしておりますのでお気軽にご相談ください。

カレンダー

 123456
78910111213
14151617181920
21222324252627
28293031   
       
      1
2345678
9101112131415
16171819202122
23242526272829
30      
   1234
567891011
12131415161718
19202122232425
262728293031 
       
1234567
891011121314
15161718192021
22232425262728
293031    
       
     12
3456789
10111213141516
17181920212223
24252627282930
       
  12345
6789101112
13141516171819
20212223242526
2728293031  
       
1234567
891011121314
15161718192021
22232425262728
2930     
       
    123
45678910
11121314151617
18192021222324
25262728293031
       
   1234
567891011
12131415161718
19202122232425
26272829   
       
    123
45678910
11121314151617
18192021222324
25262728293031
       
  12345
6789101112
13141516171819
20212223242526
27282930   
       
      1
2345678
9101112131415
16171819202122
23242526272829
3031     
    123
45678910
11121314151617
18192021222324
252627282930 
       
 123456
78910111213
14151617181920
21222324252627
28293031   
       
     12
3456789
10111213141516
17181920212223
24252627282930
31      
   1234
567891011
12131415161718
19202122232425
2627282930  
       
1234567
891011121314
15161718192021
22232425262728
293031    
       
     12
3456789
10111213141516
17181920212223
24252627282930
       
  12345
6789101112
13141516171819
20212223242526
2728293031  
       
  12345
6789101112
13141516171819
20212223242526
2728     
       
      1
2345678
9101112131415
16171819202122
23242526272829
3031     
   1234
567891011
12131415161718
19202122232425
262728293031 
       
 123456
78910111213
14151617181920
21222324252627
282930    
       
     12
3456789
10111213141516
17181920212223
24252627282930
31      
   1234
567891011
12131415161718
19202122232425
2627282930  
       
1234567
891011121314
15161718192021
22232425262728
293031    
       
    123
45678910
11121314151617
18192021222324
25262728293031
       
  12345
6789101112
13141516171819
20212223242526
27282930   
       
      1
2345678
9101112131415
16171819202122
23242526272829
3031     
    123
45678910
11121314151617
18192021222324
252627282930 
       
 123456
78910111213
14151617181920
21222324252627
28293031   
       
  12345
6789101112
13141516171819
20212223242526
2728293031  
       
1234567
891011121314
15161718192021
22232425262728
2930     
       
    123
45678910
11121314151617
18192021222324
25262728293031
       
  12345
6789101112
13141516171819
20212223242526
27282930   
       
      1
2345678
9101112131415
16171819202122
23242526272829
3031     
   1234
567891011
12131415161718
19202122232425
262728293031 
       
 123456
78910111213
14151617181920
21222324252627
282930    
       
     12
3456789
10111213141516
17181920212223
24252627282930
31      
   1234
567891011
12131415161718
19202122232425
2627282930  
       
1234567
891011121314
15161718192021
22232425262728
293031    
       
1234567
891011121314
15161718192021
22232425262728
       
       
    123
45678910
11121314151617
18192021222324
25262728293031
       
 123456
78910111213
14151617181920
21222324252627
28293031   
       
      1
2345678
9101112131415
16171819202122
23242526272829
30      
     12
3456789
10111213141516
17181920212223
24252627282930
31      
1234567
891011121314
15161718192021
22232425262728
2930     
       
    123
45678910
11121314151617
18192021222324
25262728293031
       
  12345
6789101112
13141516171819
20212223242526
27282930   
       
      1
2345678
9101112131415
16171819202122
23242526272829
3031     
     12
3456789
10111213141516
17181920212223
242526272829 
       
  12345
6789101112
13141516171819
20212223242526
2728293031  
       
      1
2345678
9101112131415
16171819202122
23242526272829
3031     
    123
45678910
11121314151617
18192021222324
252627282930 
       
 123456
78910111213
14151617181920
21222324252627
28293031   
       
      1
2345678
9101112131415
16171819202122
23242526272829
30      
   1234
567891011
12131415161718
19202122232425
262728293031 
       
1234567
891011121314
15161718192021
22232425262728
293031    
       
     12
3456789
10111213141516
17181920212223
24252627282930
       
  12345
6789101112
13141516171819
20212223242526
2728293031  
       
1234567
891011121314
15161718192021
22232425262728
2930     
       
    123
45678910
11121314151617
18192021222324
25262728293031
       
    123
45678910
11121314151617
18192021222324
25262728   
       
 123456
78910111213
14151617181920
21222324252627
28293031   
       
     12
3456789
10111213141516
17181920212223
24252627282930
31      
   1234
567891011
12131415161718
19202122232425
2627282930  
       
1234567
891011121314
15161718192021
22232425262728
293031    
       
     12
3456789
10111213141516
17181920212223
24252627282930
       
  12345
6789101112
13141516171819
20212223242526
2728293031  
       
      1
2345678
9101112131415
16171819202122
23242526272829
3031     
    123
45678910
11121314151617
18192021222324
252627282930 
       
 123456
78910111213
14151617181920
21222324252627
28293031   
       
      1
2345678
9101112131415
16171819202122
23242526272829
30      
   1234
567891011
12131415161718
19202122232425
262728293031 
       
   1234
567891011
12131415161718
19202122232425
262728    
       
1234567
891011121314
15161718192021
22232425262728
293031    
       
    123
45678910
11121314151617
18192021222324
25262728293031
       
  12345
6789101112
13141516171819
20212223242526
27282930   
       
      1
2345678
9101112131415
16171819202122
23242526272829
3031     
    123
45678910
11121314151617
18192021222324
252627282930 
       
 123456
78910111213
14151617181920
21222324252627
28293031   
       
     12
3456789
10111213141516
17181920212223
24252627282930
31      
   1234
567891011
12131415161718
19202122232425
2627282930  
       
1234567
891011121314
15161718192021
22232425262728
293031    
       
     12
3456789
10111213141516
17181920212223
24252627282930
       
  12345
6789101112
13141516171819
20212223242526
2728     
       
      1
2345678
9101112131415
16171819202122
23242526272829
3031     
   1234
567891011
12131415161718
19202122232425
262728293031 
       
 123456
78910111213
14151617181920
21222324252627
282930    
       
1234567
891011121314
15161718192021
22232425262728
293031    
       
    123
45678910
11121314151617
18192021222324
25262728293031
       
  12345
6789101112
13141516171819
20212223242526
27282930   
       
      1
2345678
9101112131415
16171819202122
23242526272829
3031     
    123
45678910
11121314151617
18192021222324
252627282930 
       
 123456
78910111213
14151617181920
21222324252627
28293031   
       
1234567
891011121314
15161718192021
22232425262728
29      
       
    123
45678910
11121314151617
18192021222324
25262728293031
       
 123456
78910111213
14151617181920
21222324252627
28293031   
       
      1
2345678
9101112131415
16171819202122
23242526272829
30      
   1234
567891011
12131415161718
19202122232425
262728293031 
       
 123456
78910111213
14151617181920
21222324252627
282930    
       
    123
45678910
11121314151617
18192021222324
25262728293031
       
  12345
6789101112
13141516171819
20212223242526
27282930   
       
      1
2345678
9101112131415
16171819202122
23242526272829
3031     
      1
2345678
9101112131415
16171819202122
232425262728 
       
   1234
567891011
12131415161718
19202122232425
262728293031 
       
1234567
891011121314
15161718192021
22232425262728
293031    
       
     12
3456789
10111213141516
17181920212223
24252627282930
       
  12345
6789101112
13141516171819
20212223242526
2728293031  
       
1234567
891011121314
15161718192021
22232425262728
2930     
       
    123
45678910
11121314151617
18192021222324
25262728293031
       
 123456
78910111213
14151617181920
21222324252627
28293031   
       
      1
2345678
9101112131415
16171819202122
23242526272829
30      
   1234
567891011
12131415161718
19202122232425
262728293031 
       
 123456
78910111213
14151617181920
21222324252627
282930    
       
     12
3456789
10111213141516
17181920212223
2425262728  
       
  12345
6789101112
13141516171819
20212223242526
2728293031  
       
      1
2345678
9101112131415
16171819202122
23242526272829
3031     
    123
45678910
11121314151617
18192021222324
252627282930 
       
 123456
78910111213
14151617181920
21222324252627
28293031   
       
      1
2345678
9101112131415
16171819202122
23242526272829
30      
   1234
567891011
12131415161718
19202122232425
262728293031 
       
1234567
891011121314
15161718192021
22232425262728
293031    
       
U・Iターン転職しませんか?
群馬データセンター
群馬の法人ITサポートサービス Wide Net[ワイドネット]
Wide Netのクラウドバックアップ
クラウド型ファイル共有サービスRushDrive
ワイドオフィス
ネディアのSDGsへの取り組み