ヒント: 新しい Xlookup 関数と xlookup 関数を使って、この記事で説明されている関数の強化されたバージョンを試してみてください。 これらの新しい関数は、どのような方向でも動作し、既定では完全一致が返されます。これは、先行タスクよりも簡単で使いやすいものになります。
オフィスの所在地番号のリストがあり、どの従業員がオフィスにいるかを知る必要があるとします。 スプレッドシートは非常に大きなものであるため、タスクが困難であると考えられます。 Lookup 関数を使用すると、実際には非常に簡単です。
VLOOKUP関数とHLOOKUP関数は、 INDEXとMATCHと共に、Excel で最も有用な関数の一部です。
注: ルックアップウィザード機能は、Excel では使用できなくなりました。
VLOOKUP の使用方法の例を次に示します。
=VLOOKUP(B2,C2:E7,3,TRUE)
この例では、B2 は最初の 引数(関数が動作する必要があるデータの要素) です。 VLOOKUP の場合、この最初の引数は、検索する値です。 この引数には、セル参照、または "smith" や21000などの固定値を指定できます。 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 を使用する方法の例については、「Bill by Jelen 氏、Microsoft MVP 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 |
