XLOOKUP関数の使い方を手順で解説
XLOOKUP関数は、ある列で値を探し、見つかった行の、別の列の値を返す関数です。エクセル(Excel)で、社員番号からメールアドレスを出す、商品コードから価格を出す、重さから送料を出す、といった作業に使います。Microsoft 365とExcel 2021以降で使えます。Excel 2019以前にはないため、#NAME?になります。
=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])
- 検索値:探す値です。
F2のようなセルを指定するのが一般的です。 - 検索範囲:検索値を探す1つの列です。
- 戻り範囲:返す値がある列です。検索範囲と同じ行数にします。
- 見つからない場合:一致がないときに表示する値です。省略すると
#N/Aになります。 - 一致モード:省略すると完全一致です。区分の表では
-1(完全一致、なければ次に小さい値)を使います。 - 検索モード:省略すると上から下へ探します。
使い方の例
A1:D5に社員の一覧があり、F2の社員番号からメールアドレスを出したいとします。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 社員番号 | 氏名 | 部署 | メールアドレス |
| 2 | E-1021 | 佐藤 美咲 | 経理 | [email protected] |
| 3 | E-1034 | 田中 健 | 営業 | [email protected] |
| 4 | E-1047 | 鈴木 彩 | 人事 | [email protected] |
| 5 | E-1052 | 高橋 翔 | 営業 | [email protected] |
=XLOOKUP(F2, $A$2:$A$5, $D$2:$D$5)
F2がE-1047なら、XLOOKUPはA列でその番号を見つけ、同じ行のD列の[email protected]を返します。VLOOKUPのように列番号を数える必要はなく、返す列を直接指定します。そのため、表の途中に列が挿入されても、違う列を返すことはありません。
左方向の検索
検索範囲と戻り範囲はそれぞれ独立しているので、探す列より左の列も返せます。氏名から社員番号を出すなら、=XLOOKUP(F3, $B$2:$B$5, $A$2:$A$5)です。VLOOKUPでは表の最初の列でしか探せないため、この検索にはINDEXとMATCHの組み合わせが必要でした。
見つからない場合
4番目の引数に値を指定すると、一致がないときにその値を表示します。IFNAで包む必要はありません。
=XLOOKUP(F2, $A$2:$A$5, $D$2:$D$5, "名簿にありません")
#N/Aのままにすると、合計や後続の数式までエラーになります。見つからないことが想定される一覧では、読めるメッセージか0を返すようにします。それでも見つかるはずの値が見つからないときは、末尾の空白や、数値と文字列の数字の違いを疑ってください。
区分の表で使う(近似一致)
送料が重さの区分で決まる表では、重さがちょうど一致する行はありません。一致モードに-1を指定すると、完全一致がなければ次に小さい値の行を返します。
| H | I | |
|---|---|---|
| 1 | 重さ(kg)以上 | 送料 |
| 2 | 0 | 600 |
| 3 | 2 | 900 |
| 4 | 5 | 1,400 |
| 5 | 10 | 2,100 |
=XLOOKUP(K2, $H$2:$H$5, $I$2:$I$5, , -1)
K2が6.5なら、5の行の1,400を返します。VLOOKUPの近似一致と違い、区分の表が小さい順に並んでいなくても正しく動きます。見つからない場合の引数は空けておき、カンマだけを残します。最後に一致した行を返す検索モードや、ワイルドカードによる部分一致は、XLOOKUPの応用のレッスンで練習します。
このレッスンの練習問題は、Excelのように動くスプレッドシートで解きます。数式を入力してEnterを押すと、セルの結果で答え合わせをするので、正しい数式であればどの書き方でも正解になります。