VLOOKUPで検索する
VLOOKUPの最後の引数を省くと、ExcelもGoogle スプレッドシートも近似一致を使います。GoogleはFALSEと入力することをすすめています。
コードつきの価格表
ある店は小さな表に価格をまとめています。A2:A5にコード、B2:B5に品名、C2:C5に価格です。レジでだれかがコードを入力し、価格を知りたいとします。これがVLOOKUPの仕事です。表の最初の列で値を探し、同じ行から何かを返します。Microsoftは必要な四つの要素を挙げています。検索する値、その値がある範囲、その範囲の中で答えがある列の番号、そして近似一致（TRUE）と完全一致（FALSE）のどちらにするかです。=VLOOKUP(104, A2:C5, 3, FALSE)はA列で104を探し、範囲の3列目から価格を返します。
トラブルの多くは二つの決まりから起こります。一つ目に、探す値は範囲の最初の列になければならず、答えはその右側になければなりません。VLOOKUPは決して左を見ないので、コードがB列にあるなら範囲はBから始めます。二つ目に、列番号はシートのA列からではなく、範囲の最初の列から数えます。B2:D9ならBが1、Cが2、Dが3です。どちらのヘルプページも、コピーする数式について同じ助言をしています。$A$2:$C$5のように範囲を絶対参照にして、下方向へコピーしてもずれないようにしましょう。
コードが一覧にないとき
FALSEなら、VLOOKUPは指定した値とぴったり同じものを探し、一致する最初の行を返します。一致がなければ#N/Aです。列番号が範囲の列数より大きければ#REF!、1未満なら#VALUE!になります。文字列の検索は大文字と小文字は区別しませんが、スペースは区別します。Googleのヘルプが指摘するように、" Apple"、"Apple "、"Apple"は三つの別々の値です。文字列として保存された数値も数値とは別物なので、'104と入力したコードは104では見つかりません。
完全一致では、どちらのソフトでもワイルドカード?と*が使えます。?は任意の1文字、*は任意の文字の並びを表すので、"Ora*"でOrangeが見つかります。まだ価格表にないコードのように#N/Aがふつうに起こりうる結果なら、どちらのソフトにもあるIFNAで検索を包みましょう。=IFNA(VLOOKUP(E2, $A$2:$C$5, 3, FALSE), "not found")。IFERRORでも動きます。ただしIFERRORは、見えてほしいまちがった列番号も含め、あらゆる種類のエラーを隠してしまいます。Microsoftの#VALUE!のページも、同じ理由でIFERRORに注意をうながしています。
ドラッグで回転、スクロールまたはピンチで拡大・縮小。
決まったコードではなく区間を検索する
TRUE、または4番目の引数をまったく書かないと近似一致になり、どちらのソフトでもこれが既定です。Googleのヘルプは検索のしかたを説明しています。最初の列を下へたどり、検索キーより大きい値が見つかったら、その一つ前の行で止まります。これは区間に向いています。最初の列に0、60、70、80、90がある成績表では、85点は80を通り過ぎて90に出会い、80の行からBを返します。最初の列は小さい順に並べておく必要があり、最初の行より小さい値は#N/Aを返します。並べていない列では答えを信用できません。だからGoogleは、範囲を意図するのでなければFALSEをすすめています。
ドラッグで回転、スクロールまたはピンチで拡大・縮小。
| Microsoft Excel | Google スプレッドシート | |
|---|---|---|
| 4番目の引数の名前 | range_lookup | is_sorted |
| 省略したときの既定 | TRUE（近似一致） | TRUE（近似一致） |
| ヘルプページの助言 | 最初の列を並べ替えるか、FALSEを使う | FALSEをすすめる |
| XLOOKUP | Microsoft 365とExcel 2021以降。2016と2019にはない | 使える |
| 読み取れない数式 | メッセージを出して受け付けない | #ERROR! |
| 循環参照 | 警告のあと、0または最後の値 | #REF! |
| 桁数なしのROUND | 受け付けない | 整数に丸める |
| 関数ごとの引数の数 | 255個まで | いくつでも |
今はどちらのソフトにもXLOOKUPがあります。検索範囲と結果の範囲を別々にとるので、答えが左側にあってもかまいません。また、指定しない限り完全一致を使います。MicrosoftによるとXLOOKUPはExcel 2016とExcel 2019では使えないので、それらの版で開く必要があるファイルはVLOOKUPのままにしておきましょう。上の表は、このコースで出会った二つのソフトのちがいをまとめたものです。
- コードを検索するときにFALSEを省く。 既定は近似一致なので、一覧にないコードでも#N/Aではなく、一つ上の行の価格が返ってくることがあります。
- 列番号をシートのA列から数える。 列番号は範囲の最初の列から数えます。C2:F20なら、E列は5ではなく3です。
- 範囲に$をつけずに検索を下方向へコピーする。 A2:C5がA3:C6、A4:C7とずれていき、下の行では表の上の方にあるコードが見つからなくなります。
- すべての検索をIFERRORで包む。 予想どおりの#N/Aだけでなく、まちがった列番号による#REF!まで隠してしまいます。IFNAが隠すのは#N/Aだけです。
- A2:B5に、コード101、103、104、107と品名Apple、Banana、Orange、Pearがあります。=VLOOKUP(103, A2:B5, 2, FALSE)は何を返しますか？
- 同じ表です。=VLOOKUP(102, A2:B5, 2, FALSE)は何を返しますか？
- #N/A
- Apple
- Banana
- #REF!
- 同じ表です。=VLOOKUP(102, A2:B5, 2, TRUE)は何を返しますか？
- 同じ表です。=VLOOKUP(103, A2:B5, 3, FALSE)は何を返しますか？
- #REF!
- #N/A
- #VALUE!
- Banana
- 範囲はC2:F20です。E列の値を返す列番号はいくつですか？
- 成績表：0 F、60 D、70 C、80 B、90 A。85に対して近似一致のVLOOKUPは何を返しますか？
- 同じ表です。ちょうど90なら何を返しますか？
- 4番目の引数を省くとどうなりますか？
- どちらのソフトでも近似一致
- どちらのソフトでも完全一致
- Excelでは完全一致、スプレッドシートでは近似一致
- エラーになる
- 表の範囲を$A$2:$C$5と書くべきなのはなぜですか？
- 数式を下方向へコピーしてもずれないように
- 表を並べ替えるために
- 大文字だけに一致させるために
- 文字列を返すために
- 品名がB2:B5に、価格がC2:C5にあります。=IFNA(VLOOKUP("Pencil", B2:C5, 2, FALSE), "not found")は何を返しますか？
- XLOOKUPがないExcelの版はどれですか？
- Excel 2016とExcel 2019
- Microsoft 365
- Web版Excel
- Excel 2021
- 完全一致にはVLOOKUP(値, 範囲, 列番号, FALSE)。
- 値は最初の列になければならない。列番号は範囲から数える。
- TRUE（または省略）は近似一致：最初の列を並べておく必要があり、値より大きくない最後の行を返す。
- 範囲は絶対参照に、予想される#N/AにはIFNAを、区間を意図しない限りFALSEを使う。
このコースの出典 (19)
- Microsoft Support, "Switch between relative, absolute, and mixed references" (support.microsoft.com/en-us/office/dfec08cd-ae65-4f56-839e-5f0d8d0baca9: $ before column and row, F4 to switch, copying two down and two right: $A$1 stays, A$1 to C$1, $A1 to $A3, A1 to C3)
- Microsoft Support, "AVERAGE function" (support.microsoft.com/en-us/office/047bac88-d466-426c-a32b-8f33eb960cf6: text, logical values and empty cells in a reference ignored, zeros included; 10, 7, 9, 27, 2 gives 11, with 5 gives 10; mean 5, median 4, mode 3 for 2 3 3 5 7 10)
- Microsoft Support, "COUNT function" (support.microsoft.com/en-us/office/a59cd7fc-b623-4d93-87a4-d23bf411294c: only numbers counted, dates count, errors and text do not)
- Microsoft Support, "ROUND function" (support.microsoft.com/en-us/office/c018c5d8-40fb-4053-90b1-b3e7f61a213c: num_digits required; 2.15 to 2.2, -1.475 to -1.48, 626.3 to 1000, 1.98 to 0, -50.55 to -100)
- Microsoft Support, "IF function" (support.microsoft.com/en-us/office/69aed7c9-4e8a-4755-a9bc-aa8bbff73be2: value_if_false optional, text in quotes except TRUE and FALSE, 0 and #NAME? problems)
- Microsoft Support, "VLOOKUP function" (support.microsoft.com/en-us/office/0bbc8083-26fe-4963-8ab8-93a18ad188a1: range_lookup TRUE by default, sorted first column, #N/A, #REF!, #VALUE!, absolute table range, wildcards with FALSE)
- Microsoft Support, "XLOOKUP function" (support.microsoft.com/en-us/office/b7fd680e-6d10-43e6-84f9-88eae8bf5929: not in Excel 2016 and Excel 2019, exact match by default)
- Microsoft Support, "How to correct a #VALUE! error" (support.microsoft.com/en-us/office/15e1b616-fbf2-4147-9c0b-0a11a20e409e: + and * may not calculate cells with text, use SUM instead, IFERROR hides all errors)
- Microsoft Support, "Excel specifications and limits" (15 digits, 64 nested levels, 255 arguments)
- Google Docs Editors Help, "VLOOKUP" (support.google.com/docs/answer/3093318: is_sorted TRUE by default, FALSE recommended, search down to a larger value, " Apple" and "Apple " differ, wildcards, IFNA, absolute range)
- Google Docs Editors Help, "XLOOKUP" (support.google.com/docs/answer/12405947)
- Google Docs Editors Help, "ROUND" (support.google.com/docs/answer/3093440: places optional, 0 by default, rounding by magnitude)
- Google Docs Editors Help, "IF" (support.google.com/docs/answer/3093364: order of the answers is the most common problem, IFS)
- Google Docs Editors Help, "AVERAGE" (support.google.com/docs/answer/3093615: text ignored, any number of arguments)
- Google Docs Editors Help, "COUNTA" (support.google.com/docs/answer/3093991: zero-length strings and spaces count)
- Google Docs Editors Help, "Keyboard shortcuts for Google Sheets" (support.google.com/docs/answer/181110: F4 and Fn+F4 while entering a formula, F4 as Redo)
- Pryor Learning and Exceljet (F4 cycle A1, $A$1, A$1, $A1; = comparison not case-sensitive)
- Ablebits (IF without value_if_false returns FALSE)
- Spreadsheet Class (Sheets circular dependency shows #REF!)
確認日: 2026-10-19