ダイナミックコルインデックスとVLOOKUP
`link：/ forms-and-functions-introduction-of-vlookup-function [VLOOKUP function]`では、col_index_nostaticを定義することがよくあります。 VLOOKUP（id、data、3,0）のように、VLOOKUP式内にハードコーディングします。この問題は、データ内の列を挿入または削除するときに発生します。 3番目の列の前後に列を削除または追加すると、3番目の列は目的の列を参照しなくなります。これは1つの問題です。その他は、検索する列が複数ある場合です。各数式の列インデックスを編集する必要があります。単純なコピー貼り付けは役に立ちません。
しかし、VLOOKUPに見出しを調べて、一致する見出しの値のみを返すように指示できる場合はどうでしょうか。これは双方向VLOOKUPと呼ばれます。
たとえば、marks列にVLOOKUP式がある場合、VLOOKUPはデータ内のmarks列を検索し、その列から値を返す必要があります。これで問題は解決します。
うーん…さて、どうやってそれをするのですか？ `link：/ forms-and-functions-introduction-of-vlookup-function [VLOOKUPfunction]`内で `link：/ lookup-formulas-excel-match-function [the MatchFunction]`を使用する。
一般式
=VLOOKUP(lookup_value,table_array,MATCH(lookup_heading,table_headings,0),0)
Lookup_value：table_arrayの最初の列のルックアップ値。
Table_array：ルックアップを実行する範囲。例：A2、D10。
Lookup_heading：table_arrayの見出しで検索する見出し。
Table_headings：テーブル配列の見出しの参照。例えば。テーブルがA2、D10であり、各列の上部に見出しがある場合、そのA1：D1。
これで、動的col_indexに必要なものがわかったので、例を使用してすべてをクリアしてみましょう。
動的VLOOKUPの例この例では、範囲A4：E16の学生のデータを含むこのテーブルがあります。
ロール番号と見出しを使用して、このテーブルからデータを取得したいと思います。この例では、セルH4で、セルG4に書き込まれていないロールのデータとH3の見出しのデータを取得します。見出しを変更すると、それぞれの範囲のデータがセルH4で取得されます。
この数式をセルH4に記述します
=VLOOKUP(G4,B4:E16,MATCH(H3,B3:E3,0),0)
テーブル配列はB4：E16であるため、見出し配列はB3：E3になります。
注：データが適切に構造化されている場合、列見出しの列数は同じになり、テーブルの最初の行になります。
仕組み：
したがって、主要な部分は、列のインデックス番号を自動的に評価することです。
そのために、MATCH関数を使用しました。
MATCH（H3、B3：E3,0）：H3には「学生」が含まれているため、MATCHは2を返します。
H3に「グレード」があった場合、4が返されます。 VLOOKUP数式には、最終的にcol_index_numが含まれます。
=VLOOKUP(G4,B4:E16,2,0)
ご存知のように、 link：/ lookup-formulas-excel-match-function [MATCH]
関数は、指定された1次元範囲内の指定された値のインデックス番号を返します。したがって、 `link：/ lookup-formulas-excel-match-function [MATCH]`は、H3でB3：E3の範囲に書き込まれた値を検索し、そのインデックス番号を返します。
これで、H3で見出しを変更するときはいつでも、見出しにある場合、この数式はそれぞれの列から値を返します。そうしないと、＃N / Aエラーが発生します。
複数の列のVLOOKUPをすばやく
上記の例では、1つの列値からの回答が必要でした。しかし、一度に複数の列を取得したい場合はどうでしょうか。上記の数式をコピーすると、エラーが返されます。ポータブルにするために、いくつかの小さな変更を加える必要があります。
VLOOKUPで `link：/ excel-range-name-absolute-reference-in-excel [AbsoluteReferences]`を使用するセルH2に以下の数式を記述します。
=VLOOKUP($G2,$B$2:$E$14,MATCH(H$1,$B$1:$E$1,0),0)
次に、H2：J6の範囲内のすべてのセルにH2をコピーして、データを入力します。
仕組み：
ここでは、VLOOKUP（$ G2）のルックアップ値の行を除く各範囲の `link：/ excel-range-name-absolute-reference-in-excel [absolutereference]`を指定しました
MATCH（H $ 1）のlookup_valueの列。
$ G2：これにより、下向きにコピーするときにVLOOKUP関数のルックアップ値の行を変更できますが、右にコピーすると列の変更が制限されます。これにより、VLOOKUPは相対行のみでG列からIDを検索します。
同様に、H $ 1を使用すると、水平方向にコピーすると列が変更され、下方向にコピーすると行が制限されます。
名前付き範囲の使用
上記の例は正常に機能しますが、この式の読み取りと書き込みが困難になります。そして、これはまったくポータブルではありません。これは、名前付き範囲を使用して簡略化できます。
最初にここでいくつかの命名を行います。この例では、$ B $ 2：$ E $ 14：データとして$ B $ 1：$ E $ 1：見出しとしてH $ 1：見出しとして名前を付けます。列を相対的にします。これを行うには、H1を選択します。 Ctrlキーを押しながらF3キーを押し、[新規]をクリックします。[参照]セクションで、Hの前から「$」を削除します。
$ G2：同様に、RollNoという名前を付けます。今回は、2の前から「$」を削除して行を相対化します。
シートにすべての名前が記載されたら、この数式をExcelファイルの任意の場所に記述します。それは常に正しい答えを得るでしょう。
=VLOOKUP(RollNo,Data, MATCH(Heading, Headings,0),0)
ほら、誰でもこれを読んで理解することができます。
したがって、これらのメソッドを使用して、col_index_numを動的にすることができます。以下のコメントセクションで、これが役に立ったかどうか教えてください。
関連記事：
link：/ excel-range-name-all-about-named-ranges-in-excel [Excelの名前付き範囲]
link：/ lookup-formulas-how-to-vlookup-from-different-excel-sheet [別のExcelシートからVLOOKUPする方法]
link：/ lookup-formulas-vlookup-multiple-values [VLOOKUP Multiple Values]
人気の記事
link：/ keyboard-formula-shortcuts-50-excel-shortcuts-to-increase-your-productivity [生産性を高めるための50Excelショートカット]：タスクをより速くします。これらの50のショートカットにより、Excelでの作業がさらに高速になります。
link：/ forms-and-functions-introduction-of-vlookup-function [ExcelでVLOOKUP関数を使用する方法]：これは、さまざまな範囲から値を検索するために使用される、Excelで最も使用され人気のある関数の1つです。とシート。
link：/ Tips-countif-in-microsoft-excel [ExcelでCOUNTIF関数を使用する方法]：この驚くべき関数を使用して条件付きの値をカウントします。特定の値をカウントするためにデータをフィルタリングする必要はありません。ダッシュボードを準備するには、Countif関数が不可欠です。
link：/ excel-formula-and-function-excel-sumif-function [ExcelでSUMIF関数を使用する方法]：これはもう1つのダッシュボードの必須関数です。これは、特定の条件で値を合計するのに役立ちます。