ヒント
この記事で説明する関数の改良バージョンである、新しい XLOOKUP 関数と XMATCH 関数を使用してみてください。 これらの新しい関数は任意の方向で機能し、既定で完全一致を返すことで、以前の関数よりも使いやすく便利になっています。
オフィスの場所の番号のリストがあり、各オフィスにどの従業員がいるかを知る必要があるとします。 スプレッドシートは巨大なので、難しい作業だと思うかもしれません。 実際には、ルックアップ関数を使用して行うのは非常に簡単です。
VLOOKUP 関数と HLOOKUP 関数は、INDEX および MATCH とともに、Excel で最も便利な関数の一部です。
注
ルックアップ ウィザード機能は、Excel では使用できなくなりました。
VLOOKUP の使用方法の例を次に示します。
=VLOOKUP(B2,C2:E7,3,TRUE)
この例では、B2 が最初の 引数で、関数が機能するために必要なデータ要素です。 VLOOKUP の場合、この最初の引数は検索する値です。 この引数には、セル参照、または "smith" や 21,000 などの固定値を指定できます。 2 番目の引数は、検索する値を検索するセルの範囲 C2-:E7 です。 3 番目の引数は、検索する値を含むセル範囲内の列です。
4 番目の引数は省略できます。 TRUE または FALSE のいずれかを入力します。 「TRUE」を入力するか、この引数を空白のままにすると、最初の引数で指定した値に最も近いものが関数の結果として返されます。 FALSE を入力すると、関数は最初の引数で指定された値と一致します。 つまり、4 番目の引数を空白のままにするか、TRUE を入力すると、柔軟性が向上します。
この例では、この関数のしくみを説明します。 セル B2 (最初の引数) に値を入力すると、VLOOKUP は範囲 C2:E7 (2 番目の引数) 内のセルを検索し、範囲の 3 番目の列 E (第 3 の引数) から最も近似したセルを返します。
4 番目の引数は空であるため、関数は近似一致を返します。 そうでない場合は、列 C または D の値の 1 つを入力しなければ結果が何も返されません。
VLOOKUP に慣れている場合は、HLOOKUP 関数も同様に簡単に使用できます。 同じ引数を入力しますが、列ではなく行で検索されます。
VLOOKUP の代わりに INDEX と MATCH を使用する
VLOOKUP の使用には一定の制限があり、VLOOKUP 関数では左から右にしか値を検索できません。 つまり、調べる値を含む列は、戻り値を含む列の左側に常に配置する必要があります。 この方法でスプレッドシートが作成されていない場合は、VLOOKUP を使用しないでください。 代わりに、INDEX 関数と MATCH 関数を組み合わせて使用してください。
この例では、検索する値であるシカゴが左端の列にない小さいリストについて説明します。 そのため、VLOOKUP を使うことはできません。 代わりに、MATCH 関数を使って範囲 B1:B11 でシカゴを検索します。 シカゴは行 4 にあります。 次に、INDEX ではその値を引数として使用して、4 番目の列 (列 D) にあるシカゴの人口を検索します。 使用された数式はセル A14 に表示されます。
試してみましょう
独自のデータで検索関数を試す前に試してみたい場合は、次のサンプル データをいくつか示します。
職場での VLOOKUP の例
次のデータを空白のスプレッドシートにコピーします。
ヒント
データを Excel に貼り付ける前に、列 A から C の列幅を 250 ピクセルに設定し、[ 文字列の折り返し ] ([ホーム ] タブの [配置] グループ) をクリックします。
| 濃度 | 粘度 | 温度 |
|---|---|---|
| 0.457 | 3.55 | 500 |
| 0.525 | 3.25 | 400 |
| 0.606 | 2.93 | 300 |
| 0.675 | 2.75 | 250 |
| 0.746 | 2.57 | 200 |
| 0.835 | 2.38 | 150 |
| 0.946 | 2.17 | 100 |
| 1.09 | 1.95 | 50 |
| 1.29 | 1.71 | 0 |
| 数式 | 説明 | 結果 |
| =VLOOKUP(1,A2:C10,2) | 近似一致を使って、列 A で値 1 を検索し、列 A で 1 以下の最大値である 0.946 を見つけて、同じ行の列 B の値を返します。 | 2.17 |
| =VLOOKUP(1,A2:C10,3,TRUE) | 近似一致を使って、列 A で値 1 を検索し、列 A で 1 以下の最大値である 0.946 を見つけて、同じ行の列 C の値を返します。 | 100 |
| =VLOOKUP(0.7,A2:C10,3,FALSE) | 完全一致を使って、列 A で値 0.7 を検索します。列 A に完全一致がないため、エラーが返されます。 | #N/A |
| =VLOOKUP(0.1,A2:C10,2,TRUE) | 近似一致を使って、列 A で値 0.1 を検索します。0.1 は列 A の最小値よりも小さいため、エラーが返されます。 | #N/A |
| =VLOOKUP(2,A2:C10,2,TRUE) | 近似一致を使って、列 A で値 2 を検索し、列 A で 2 以下の最大値である 1.29 を見つけて、同じ行の列 B の値を返します。 | 1.71 |
HLOOKUP の例
次の表のすべてのセルをコピーし、Excel の空のワークシートのセル A1 に貼り付けます。
ヒント
データを Excel に貼り付ける前に、列 A から C の列幅を 250 ピクセルに設定し、[ 文字列の折り返し ] ([ホーム ] タブの [配置] グループ) をクリックします。
| 軸 | ベアリング | ボルト |
|---|---|---|
| 4 | 4 | 9 |
| 5 | 7 | 10 |
| 6 | 8 | 11 |
| 数式 | 説明 | 結果 |
| =HLOOKUP("ギア", A1:C4, 2, TRUE) | 行 1 の "ギア" を検索し、同じ列 (列 A) の行 2 の値を返します。 | 4 |
| =HLOOKUP("ベアリング", A1:C4, 3, FALSE) | 行 1 の "ベアリング" を検索し、同じ列 (列 B) の行 3 の値を返します。 | 7 |
| =HLOOKUP("ベ", A1:C4, 3, TRUE) | 行 1 の "ベ" を検索し、同じ列の行 3 の値を返します。 "ベ" に完全に一致する値が見つからないので、行 1 の中の "ベ" よりも小さい最大値である、列 A の "軸" が使用されます。 | 5 |
| =HLOOKUP("ボルト", A1:C4, 4) | 行 1 の "ボルト" を検索し、同じ列 (列 C) の行 4 の値を返します。 | 11 |
| =HLOOKUP(3, {1,2,3, "ア", "イ", "ウ", "エ", "オ", "カ"}, 2, TRUE) | 3 行の配列定数の中で数値 3 を検索し、同じ列 (ここでは 3 番目) の行 2 から値が返されます。 配列定数の中には、値の行が 3 つあり、各行はセミコロン (;) で区切られています。 "c" は 2 行目および 3 と同じ列の中にあるので、"C" が返されます。 | c |
INDEX と MATCH の例
最後の例では、INDEX 関数と MATCH 関数を併用して、5 つの都市それぞれについて最も早い請求書番号とそれに対応する日付を返します。 日付は数値として返されるため、TEXT 関数を使用して日付として書式設定します。 INDEX 関数は、実際には MATCH 関数の結果を引数として使用します。 INDEX 関数と MATCH 関数の組み合わせは各数式で 2 回使用されます。1 回目は請求書番号を返し、次に日付を返します。
次の表のすべてのセルをコピーし、Excel の空のワークシートのセル A1 に貼り付けます。
ヒント
データを Excel に貼り付ける前に、列 A から D の列幅を 250 ピクセルに設定し、[ 折り返して全体を表示する] ([ホーム ] タブの [配置 ] グループ) をクリックします。
| 販売契約高 | 区名 | 請求日 | 市区町村ごとの請求日が最も早い請求書とその日付 |
|---|---|---|---|
| 3115 | 荒川区 | 12/04/07 | ="荒川区 = "&INDEX($A$2:$C$33,MATCH("荒川区",$B$2:$B$33,0),1)& "、請求日: " & TEXT(INDEX($A$2:$C$33,MATCH("荒川区",$B$2:$B$33,0),3),"yy/mm/dd") |
| 3137 | 荒川区 | 12/04/09 | ="横浜市 = "&INDEX($A$2:$C$33,MATCH("横浜市",$B$2:$B$33,0),1)& "、請求日: " & TEXT(INDEX($A$2:$C$33,MATCH("横浜市",$B$2:$B$33,0),3),"yy/mm/dd") |
| 3154 | 荒川区 | 12/04/11 | ="船橋市 = "&INDEX($A$2:$C$33,MATCH("船橋市",$B$2:$B$33,0),1)& "、請求日: " & TEXT(INDEX($A$2:$C$33,MATCH("船橋市",$B$2:$B$33,0),3),"yy/mm/dd") |
| 3191 | 荒川区 | 12/04/21 | ="前橋市 = "&INDEX($A$2:$C$33,MATCH("前橋市",$B$2:$B$33,0),1)& "、請求日: " & TEXT(INDEX($A$2:$C$33,MATCH("前橋市",$B$2:$B$33,0),3),"yy/mm/dd") |
| 3293 | 荒川区 | 12/04/25 | ="沼津市 = "&INDEX($A$2:$C$33,MATCH("沼津市",$B$2:$B$33,0),1)& "、請求日: " & TEXT(INDEX($A$2:$C$33,MATCH("沼津市",$B$2:$B$33,0),3),"yy/mm/dd") |
| 3331 | 荒川区 | 12/04/27 | |
| 3350 | 荒川区 | 12/04/28 | |
| 3390 | 荒川区 | 12/05/01 | |
| 3441 | 荒川区 | 12/05/02 | |
| 3517 | 荒川区 | 12/05/08 | |
| 3124 | 横浜市 | 12/04/09 | |
| 3155 | 横浜市 | 12/04/11 | |
| 3177 | 横浜市 | 12/04/19 | |
| 3357 | 横浜市 | 12/04/28 | |
| 3492 | 横浜市 | 12/05/06 | |
| 3316 | 船橋市 | 12/04/25 | |
| 3346 | 船橋市 | 12/04/28 | |
| 3372 | 船橋市 | 12/05/01 | |
| 3414 | 船橋市 | 12/05/01 | |
| 3451 | 船橋市 | 12/05/02 | |
| 3467 | 船橋市 | 12/05/02 | |
| 3474 | 船橋市 | 12/05/04 | |
| 3490 | 船橋市 | 12/05/05 | |
| 3503 | 船橋市 | 12/05/08 | |
| 3151 | 前橋市 | 12/04/09 | |
| 3438 | 前橋市 | 12/05/02 | |
| 3471 | 前橋市 | 12/05/04 | |
| 3160 | 沼津市 | 12/04/18 | |
| 3328 | 沼津市 | 12/04/26 | |
| 3368 | 沼津市 | 12/04/29 | |
| 3420 | 沼津市 | 12/05/01 | |
| 3501 | 沼津市 | 12/05/06 |