ヒント: この記事で説明する関数の改善されたバージョンである、新しい 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 ではなく INDEX と MATCH を使用するその他の例については、Bill Jelen https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ Microsoft MVP に関する記事を参照してください。
試してみましょう
参照関数を試す前に、独自のデータを試す場合は、サンプル データを次に示します。
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 |
