指定日が含まれる期間の料金を探すなら、XLOOKUPの一致モードを -1 にします。「指定日と一致する開始日、なければ指定日より前で最も近い開始日」を探す近似一致です。同じ日付の行だけを探す完全一致とは使い分けます。
この記事の基本の式です。セル番地は、下で説明する例の表のものです。
=XLOOKUP(B2,$A$6:$A$8,$B$6:$B$8,"対象期間外",-1)日付検索の早見表｜完全一致・期間の検索・全行抽出の使い分け
| やりたいこと | 方法 |
|---|---|
| 指定日と同じ日付の1件を探す | XLOOKUPの完全一致、VLOOKUPのFALSE |
| 料金表の開始日から、その日の料金を探す | XLOOKUPの-1、VLOOKUPのTRUE |
| 開始日から終了日までの全行を抽出 | FILTERで上下限の条件を指定 |
この記事では、開始日ごとに料金が変わる表を使います。入力する日付は「8/10～」という文字列ではなく、年を含むExcelの日付にしてください。
まず動かしてみよう｜この記事のサンプルをその場で試せます
練習用ファイルをダウンロードしなくても、記事と同じデータ・同じセル番地のサンプルをここで動かせます。黄色いセルを書き換えると、結果がその場で変わります。各解説の最後にある「▶ この式を試す」を押すと、このツールの該当セルへ移動します。
XLOOKUPで日付が入る期間の料金を求める（いつから適用か）
B2に利用日、A6:A8に開始日、B6:B8に料金を入力します。例として同じ年の8月1日・10日・16日から、それぞれ1,200円・2,200円・1,000円になる表を用意します。
B3に入力すると、8月11日の料金は2,200円です。8月11日そのものは開始日一覧になくても、直前の8月10日が選ばれます。文字の一部を探す「部分一致」ではなく、日付の大小を使う「近似一致」です。
境界の日付で確かめる
| 利用日 | 結果 | 理由 |
|---|---|---|
| 7月31日 | 対象期間外 | 最初の開始日より前 |
| 8月1日 | 1,200円 | 開始日と一致 |
| 8月9日 | 1,200円 | 8月1日以上・8月10日未満 |
| 8月10日 | 2,200円 | 新しい料金へ切り替わる日 |
| 8月16日 | 1,000円 | 最後の開始日と一致 |
VLOOKUPで日付・期間の料金を求める（近似一致 TRUE）
開始日と終了日の両方を持つ表から探す
別の表としてA6:A8＝開始日、B6:B8＝終了日、C6:C8＝料金を用意する場合です。利用日が開始日以上・終了日以下の行を探します。
特定の日付のデータを検索・抽出する（完全一致とFILTER）
売上一覧などでB2と同じ日付の1行を検索するなら、一致モードを0にします。
▶ 完全一致の式（B3）を、上の練習ツールで試す ／ FILTERの式（E6）を、上の練習ツールで試す
【実機で検証】日付の検索で #N/A になるパターン
同じ日付を探しても、データの持ち方によって見つからないことがあります。Excel の実機で 7 通り試しました。
| 原因 | 結果 | 直し方 |
|---|---|---|
| 探す値が文字の日付 | #N/A | DATEVALUE や VALUE で日付に変換する |
| 表の日付に時刻が付いている | #N/A | 範囲を INT(A2:A4) にして時刻を切り捨てる |
| 探す日が表の最初の日より前（VLOOKUP の TRUE） | #N/A | XLOOKUP の 4 番目に「範囲外」などを指定する |
時刻が付いた日付は、見た目を日付だけにしていても一致しません。表示形式では見分けられないので、数値に直して確かめます。XLOOKUP に -1 を指定すると、最後の日より後の日付は、最後の行の値を返しました。期間の終わりも決まっている表では、結果を確かめてください。
日付検索ができない・エラーになるときの確認表
| 症状 | 主な原因 | 確認と対処 |
|---|---|---|
| 同じ日付なのに見つからない（#N/A・該当なし） | 日付が文字列になっている／一方に時刻が入っている | ISNUMBER(B2) で数値か確認する。時刻があるときは日付だけにそろえる |
| 結果が 46245 のような数字になる | 結果のセルの表示形式が日付になっていない | セルの表示形式を「日付」にする |
| 「対象期間外」になる | 最初の開始日より前の日付／年が違う | 年を含む日付で入力されているか、開始日の一覧と見比べる |
| 料金が1つ前の期間のものになる | 近似一致（TRUE・-1）で、開始日が古い順になっていない | 開始日を古い順に並べ替える（料金の列も一緒に動かす） |
次のステップ：金額や得点の範囲検索を理解する
仕様の確認：Microsoft公式・XLOOKUP関数 ／ Microsoft公式・VLOOKUP関数
最新の日付のデータを取り出すときは、最新日付のデータを抽出する方法もあわせてご覧ください。
コメント