はじめに
先日、スプレットシートの一覧表から複数の列から特定の文字をキーに必要な情報だけ別シートに反映させてほしいといった依頼が来た。
そこでVlookupでできるだろうと試したところ、検索範囲より左側にも検索キーがありドはまり
結果的に、Xlookup関数により救われたのでまとめたいと思います。
VLOOKUP・XLOOKUP・HLOOKUPの違い
元になる表を検索して、その中から合致するデータを見つけてきて転記する
その１：検索範囲の指定方法
3つの関数の大きな違いは「検索する方向」にあります。
| 関数 | 検索範囲 |
|---|---|
| Vlookup | 指定したセル範囲内で検索値を縦方向に探す |
| Hlookup | 指定したセル範囲内で検索値を横方向に探す |
| Xlookup | 指定したセル範囲内で検索値を縦・横に探す |
VLOOKUPとHlOOKUP関数の頭文字にはそれぞれ下記の意味があるらしい。
VLOOKUP：「V」はVertical（垂直な）
HLOOKUP：「H」はHorizontal（水平な）
その２：検索結果の指定
Xlookupでは、検索値が見つからなかった場合に表示するデータを指定することができます。
| 関数 | 検索結果 |
|---|---|
| Vlookup Hlookup |
#N/Aエラーを返す |
| Xlookup | 引数に表示する値を指定することが可能 |
VlookupとHlookupでエラー表示をしたくない場合は、
IF関数やIFERROR関数などを組み合わせて使用する必要があります。
その３：一致モード
Xlookupでは、検索値に対して「完全一致」で検索したい場合の引数指定が不要に。
| 関数 | 引数 |
|---|---|
| Vlookup Hlookup |
完全一致で検索したい場合、 引数の「検索の型」にFALSEを指定する必要がある |
| Xlookup | 完全一致が既定の設定となっているため、 引数の「一致モード」を省略することができる |
VlookupとHlookupで引数の「検索の型」を省略した場合、
TRUE型となり近似値一致での検索します。
その４：複数条件で検索可能
VlookupとHlookupでは、同じ列に一致するデータが複数ある場合、#N/Aエラーを返す。
一方、Xlookupでは、関数内で「&」を使って商品番号と商品名のふたつの列を連結させた値で検索し、検索範囲も連結させて検索することができる。
Xlookup
書式
=XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
範囲または列ごとに情報を検索し、該当するデータを取り出す関数です。
- 検索値：どのデータで
- 検索範囲：どこを検索して（配列または範囲）
- 戻り範囲：どの範囲の値を取り出すか
- 見つからない場合：検索値が見つからない場合何を表示するか（省略した場合：「#N/A」）
- 一致モード：完全一致か、近似値も検索するかを選択 ※1
- 検索モード：どのような順序で検索するかを選択 ※2
※1：一致モード
| 設定値 | 説明 |
|---|---|
| 0 または 省略 |
完全一致。 見つからない場合は、#N/Aエラーを表示（既定の設定） |
| -1 | 完全一致。 見つからない場合は、次に小さい項目を表示 |
| 1 | 完全一致。 見つからない場合は、次に大きい項目を表示 |
| 2 | *、?、~（チルダ）などのワイルドカードとの一致。 |
※2：検索モード
| 設定値 | 説明 |
|---|---|
| 1 または 省略 |
先頭から末尾に向かって検索（既定の設定） |
| -1 | 末尾から先頭へ逆方向に検索 |
| 2 | 昇順に入れ替えられた検索範囲を使ってバイナリ検索 |
| -2 | 降順に入れ替えられた検索範囲を使ってバイナリ検索 |
バイナリ検索とは、真ん中から半分に分けて条件に合わない方を消すという作業を複数回繰り返すことで候補を絞り込んでいく検索方法。
参考