Excel2016でVLOOKUPおよびHLOOKUP関数を使用する方法

Excel 2016ルックアップ関数の中で最も人気のあるものは、HLOOKUP（水平ルックアップ用）およびVLOOKUP（垂直ルックアップ用）関数です。これらの関数は、リボンの[数式]タブの[ルックアップと参照]ドロップダウンメニュー、および[関数の挿入]ダイアログボックスの[ルックアップと参照]カテゴリにあります。これらは、データテーブルで値を検索することで値を返すことができる強力な関数グループの一部です。

VLOOKUP関数は、ルックアップテーブルの左端の列を垂直方向（上から下）に検索し、プログラムが検索している値と一致するかそれを超える値を見つけます。HLOOKUP関数は、ルックアップテーブルの最上行を水平方向（左から右）に検索して、検索している値と一致する値またはそれを超える値を見つけます。

VLOOKUP関数は次の構文を使用します。

VLOOKUP（lookup_value、table_array、col_index_num、[range_lookup]）

HLOOKUP関数は、ほぼ同じ構文に従います。

HLOOKUP（lookup_value、table_array、row_index_num、[range_lookup]）

どちらの関数でも、lookup_value引数はルックアップテーブルで検索する値であり、table_arrayは、検索する値と返す関連値の両方を含むルックアップテーブルのセル範囲または名前です。

col_index_numの引数は、値マッチングに基づくVLOOKUP関数によって返される値を含むルックアップテーブルの列を指定する検索値table_array引数のものに対する引数。垂直ルックアップテーブルの最初の列からこの列が右側にある列の数をカウントするcol_index_num引数を決定し、ルックアップテーブルの最初の列をこのカウントに含めます。

row_index_numの引数の値を含む行が返される指定 水平なテーブルにHLOOKUP関数によって。row_index_num引数は、水平ルックアップテーブルの一番上の行からこの行の下にある行数を数えることによって決定します。ここでも、ルックアップテーブルの一番上の行をこのカウントに含めます。

VLOOKUP関数とHLOOKUP関数にcol_index_numまたはrow_index_num引数を入力する場合、入力する値は、ルックアップテーブルの列または行の総数を超えることはできません。

VLOOKUP関数とHLOOKUP関数の両方のオプションのrange_lookup引数は、Excelがtable_arrayのlookup_valueに完全に一致するか近似的に一致するかを指定する論理TRUEまたはFALSEです。VLOOKUP関数またはHLOOKUP関数でTRUEを指定するか、range_lookup引数を省略すると、Excelは近似一致を検出します。range_lookup引数としてFALSEを指定すると、Excelは完全一致のみを検索します。

近似一致の検索は、垂直または水平ルックアップテーブルの最初の列または行で（テキストではなく）数値エントリを検索する場合にのみ関係します。Excelがこのルックアップ列または行で完全一致を見つけられない場合、lookup_value引数を超えない次に高い値を見つけ、col_index_numまたはrow_index_num引数で指定された列または行の値を返します。

VLOOKUPおよびHLOOKUP関数を使用する場合、ルックアップ列または行（つまり、垂直ルックアップテーブルの左端の列または水平ルックアップテーブルの一番上の行）のテキストまたは数値エントリは一意である必要があります。これらのエントリも昇順で並べ替える必要があります。つまり、テキストエントリの場合はアルファベット順、数値エントリの場合は最低から最高の順序です。

この図は、VLOOKUP関数を使用して、小切手の税引前合計に応じて、チップテーブルから15％または20％のチップを返す例を示しています。セルF3には、VLOOKUP関数が含まれています。

= VLOOKUP（Pretax_Total、Tip_Table、IF（Tip_Percentage = 0.15,2,3）） 

この数式は、セルF1のチップのパーセンテージとセルF2の小切手の税引前の金額に基づいてチップの金額を返します。

VLOOKUP関数を使用して、ルックアップテーブルから追加するチップの量を返します。

このチップテーブルを使用するには、セルF1（Tip_Percentageという名前）にチップのパーセンテージ（15％または20％）を入力し、セルF2（Pretax_Totalという名前）に税引き前の小切手の金額を入力します。次に、Excelは、ルックアップテーブルの最初の列のPretax_Totalセルに入力した値を検索します。これには、セル範囲A2：C101が含まれ、Tip_Tableという名前が付けられます。

次に、Excelは、一致するものが見つかるまでTip_Tableの最初の列の値を下に移動します。一致するものが見つかると、プログラムはVLOOKUP関数のcol_index_num引数を使用して、テーブルのその行からセルF3に戻るチップ量を決定します。Pretax_Totalセルに入力された値（この例では$ 16.50）がTip_Tableの最初の列の値のいずれかと正確に一致しないことがExcelで検出された場合、プログラムは、を超える最初の値が見つかるまで比較範囲を検索し続けます。税引前の合計（この例ではセルA19の17.00）。次に、Excelはテーブルの前の行に戻り、VLOOKUP関数のcol_index_num引数に一致する値を列に返します。（これは、オプションのrange_lookupが原因です関数から引数が省略されています。）

図のチップテーブルの例では、IF関数を使用して、セルF3のVLOOKUP関数のcol_index_num引数を決定していることに注意してください。IF関数は、Tip_Percentage（セルF1）に入力されたパーセンテージを0.15と照合することにより、チップテーブルで使用される列番号を決定します。それらが一致する場合、関数はcol_index_num引数として2を返し、VLOOKUP関数はTip_Table範囲の2番目の列（15％列B）から値を返します。それ以外の場合、IF関数はcol_index_num引数として3を返し、VLOOKUP関数はTip_Table範囲の3番目の列（20％列C）から値を返します。

次の図は、HLOOKUP関数を使用して、個別の価格ルックアップテーブルに格納されている各ベーカリーアイテムの価格を検索し、その価格を日次売上リストの価格/線量列に返す例を示しています。セルF3には、HLOOKUP関数を含む元の数式が含まれており、列Fにコピーされます。

HLOOKUP関数を使用して、ルックアップテーブルからベーカリーアイテムの価格を返します。

= HLOOKUP（item、Price_table、2、FALSE）

このHLOOKUP関数では、範囲C3：C62のItem列に指定された範囲名Itemがlookup_value引数として定義され、セル範囲I1：M2に指定されたセル範囲名Priceテーブルがtable_array引数です。row_index_numのは、Excelは、価格ルックアップテーブルの第2行、およびオプションで価格を返したいので、引数が2であるrange_lookup引数毎日販売リストの項目名は、価格ルックアップテーブルに正確に項目名と一致する必要がありますので、FALSEです。

HLOOKUP関数でPriceテーブルの範囲を使用して、Daily Salesリストの各ベーカリー商品アイテムのダースあたりの価格を入力することにより、リスト内の売上を更新するのが非常に簡単になります。この範囲でPrice / Dozコストを変更するだけで、HLOOKUP機能により、アイテムが販売される場所に関係なく、DailySalesリストの新しい価格がすぐに更新されます。