ヒント: この記事で説明する関数の改善されたバージョンである、新しい 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 番目の引数) のセルを検索し、範囲 E (3 番目の引数) の 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 の代わりに INDEX と MATCH を使用する例の詳細については、Microsoft MVP の Bill Jelen による https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ 記事を参照してください。
試してみましょう
独自のデータを使用して試す前にルックアップ関数を試す場合は、サンプル データを次に示します。
職場での 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 回使用されます。最初に、請求書番号を返してから、日付を返します。
次の表のすべてのセルをコピーし、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 |
