Excel(エクセル)のHLOOKUP関数の使い方を以下の記事で解説しました。
HLOOKUP関数(エイチルックアップ)は表を横方向検索して検索値を抽出する関数です。
HLOOKUP関数を複数のセルで利用する際、引数「行番号」をいちいち切り替えるのは面倒です。
今回の記事は、『「行番号」を自動で切り替えることが出来る時短テクニック』をご紹介します。
①ROW関数を使う方法と➁MATCH関数を使う方法の２種類の方法があります。ぞれぞれの関数の使い方も合わせてご紹介します。
VLOOKUP関数の「列番号」を自動で切り替えることが出来る時短テクニックはこちらの記事を参考にしてください。
HLOOKUP関数の「行番号」を自動で切り替える方法
- HLOOKUP関数とROW関数を組み合わせる
- HLOOKUP関数とMATCH関数を組み合わせる
HLOOKUP関数の行番号とは？
HLOOKUP関数は、「検索値」「検索範囲」「行番号」「検索方法」の４つの引数で構成されています。
＝HLOOKUP(検索値,検索範囲,行番号,[検索方法]）
その中の３つ目の引数「行番号」は、検索範囲から何行目の値を返すかを決める重要な引数です。
そのため、下の表から「商品カテゴリ」「商品名」「値段」をHLOOKUP関数で抽出する場合は、行番号を「２」「３」「4」と一つずつ変更していく必要があります。
ROW関数の使い方
HLOOKUP関数とROW関数を組み合わせた使い方を紹介する前に、まずはROW関数について紹介します。
ROW(ロウ）は英語で、「行」を意味します。
ROW関数は、指定したセルの行番号を表示することが出来ます。
＝ROW(セル番号)
例えば「＝ROW(B3)」をセルD4に挿入すると、B3セルは3行目にありますので、ROW関数の値は「3」となります。
非常にシンプルな関数ですよね。
また、今回のHLOOKUP関数との組み合わせでは利用しませんが、セル番号を指定せずにROW関数を使うと、関数を挿入したセルの行番号が返されます。
表のナンバリングに有効な手法なので、ご参考までに紹介します。
ROW関数の使い方をもっと詳しく知りたい方は、以下の記事を参考にしてみてください。
HLOOKUP関数の行番号を自動化(ROW関数と組み合わせ)
それでは、上の図の赤枠のセルに、HLOOKUP関数とROW関数を用いて、検索範囲から商品番号”25”の「商品カテゴリ」「商品名」「値段」を行番号を指定せずに表示する方法をご紹介します。
手順１．HLOOKUP関数を挿入
「商品カテゴリ」を表示したいC11セルに、HLOOKUP関数を挿入し、引数の「検索値」と「検索範囲」を指定します。引数は、下のセルにコピぺで利用出来るよう絶対参照(＄)で固定しておきましょう。
=HLOOKUP($C$10,$B$3:$I$6
手順２．３つ目の引数「行番号」にROW関数を挿入
３つ目の引数：”行番号”にROW関数を挿入します。
通常であれば引数を”２”と入力するところですが、「商品カテゴリ」行は検索範囲の上から２行目にありますので、「ROW(C2)」と入力します。セルをC2と指定していますが２行目のセルであれば、B2でもD2でもE2でもどのセルでもOKです。
=HLOOKUP($C$10,$B$3:$I$6,ROW(C2)
手順３．４つ目の引数「検索方法」を指定
最後の引数「検索方法」に”FALSE”を挿入して、「Enter」キーで数式を完成させると、商品番号”25”の「商品カテゴリ」の”薬”が表示されます。
=HLOOKUP($C$10,$B$3:$I$6,ROW(C2),FALSE)
手順４．HLOOKUP関数を下のセルにコピペ
手順３で完成したHLOOKUP関数を下のセルにもコピペすると、行番号を変更しなくても「商品名」「値段」を抽出することが出来ます。
MATCH関数は、セルの位置を調べる関数
HLOOKUP関数の行番号の切替を自動化するためには、MATCH関数を利用します。
MATCH関数(マッチ)は、検索範囲の中から検索値を検索し、その範囲内での検索値の相対的な位置を返します。
＝MATCH(検索値, 検索範囲, [照合の型])
検索値： 検索範囲の中で照合する値を指定します。
検索範囲： 検索するセルの範囲を指定します。
照合の型 ： ”-1”、”0”、”1 ”の数値のいずれかを指定しますが、”1 ”の場合は省略可能です。HLOOKUP関数と組み合わせて利用する場合は、”0”を利用します。
これだけだと、どう使うのかイメージが湧かないと思うので、具体的に下の図で使用例を説明します。
上の図では、D3セルに「=MATCH(B4,B3:B6,0)」とMATCH関数を挿入しています。
検索値のB4セル(商品カテゴリ)は、検索範囲のB3:B6の上から２番目にあるので、関数の結果は”２”と表示されます。
このように、MACH関数は特定の範囲内の中で何番目に位置しているかを数値で返すことが出来る関数です。
MATCH関数の詳しい使い方は、以下のリンク記事で紹介していますので、合わせて参考にしてみてください。
HLOOKUP関数の行番号を自動化(MATCH関数と組み合わせ)
それでは、下の図の赤枠のセルに、HLOOKUP関数とMATCH関数を用いて、検索範囲から商品番号”25”の「商品カテゴリ」「商品名」「値段」を表示する方法をご紹介します。
手順１．HLOOKUP関数を挿入
「商品カテゴリ」を表示したいC11セルに、HLOOKUP関数を挿入し、引数の「検索値」と「検索範囲」を指定します。
引数は、下のセルにコピー出来るよう絶対参照(＄)で固定しておきましょう。
=HLOOKUP($C$10,$B$3:$I$6
手順２．３つ目の引数「行番号」にMATCH関数を挿入
３つ目の引数「行番号」にMATCH関数を挿入します。
=HLOOKUP($C$10,$B$3:$I$6,MATCH(B11,$B$10:$B$13,0),
関数が長くなり一見複雑に思えるかもしれませんが、HLOOKUP関数の中に挿入しているMATCH関数の結果が”2”になるのはお分かりになりますか？
「MATCH(B11,$B$10:$B$13,0)」で、B11セルがB10からB13セルの中で何番目に位置するかを表示しようとしています。
手順３．４つ目の引数「検索方法」を指定
最後の引数「検索方法」に”FALSE”を挿入して、「Enter」キーで数式を完成させると、商品番号”25”の「商品カテゴリ」の”薬”が表示されます。
=HLOOKUP($C$10,$B$3:$I$6,MATCH(B11,$B$10:$B$13,0),FALSE)
手順４．下のセルにHLOOKUP関数をコピペ
手順３で完成したHLOOKUP関数を下のセルにもコピペします。
行番号を変更しなくても、「商品名」と「値段」が正しく表示されます。
