Excelに精通している方は、 VLOOKUP関数に精通している可能性があります。VLOOKUP関数は、同じ行内の一致するテキストに基づいて、別のセルの値を検索するために使用されます。
まだVLOOKUP関数を初めて使用する場合は、ExcelでVLOOKUPを使用する方法(how to use VLOOKUP in Excel)に関する以前の投稿を確認してください。
VLOOKUPは強力ですが、数式が機能するために一致する参照テーブルを構造化する方法に制限があります。
この記事では、VLOOKUPを使用できない場合の制限と、この問題を解決できるINDEX-MATCHと呼ばれるExcelの別の関数を紹介します。(Excel)
INDEXMATCHExcelの例
次のExcelスプレッドシートの例を使用すると、車の所有者の名前と車の名前のリストがあります。この例では、 以下に示すように、複数の所有者の下にリストされている車種(Car Model)に基づいて車IDを取得しようとします。(Car ID)
CarTypeと呼ばれる別のシートに、 ID、Car Model、Colorを含む単純な自動車データベースがあり ます。
このテーブル設定では、 VLOOKUP関数は、取得するデータが照合しようとしているものの右側の列（[車種(Car Model )]フィールド）にある場合にのみ機能します。
つまり、このテーブル構造では、車種(Car Model)に基づいて照合しようとしているため、取得できる情報は色のみです（ (Color )ID列は(ID)車種(Car Model )列 の左側にあるため IDではありません）。
これは、VLOOKUPでは、ルックアップ値が最初の列に表示され、ルックアップ列が右側にある必要があるためです。この例では、これらの条件はいずれも満たされていません。
幸いなことに、INDEX-MATCH はこれを達成するのに役立ちます。実際には、これは実際には、個別に機能する2つのExcel関数（ (Excel)INDEX関数とMATCH関数）を組み合わせたものです。
ただし、この記事では、 VLOOKUP(VLOOKUP)の機能を再現することを目的とした2つの組み合わせについてのみ説明します。
数式は最初は少し長くて威圧的に見えるかもしれません。ただし、何度か使用すると、構文を暗記することができます。
これは、この例の完全な式です。
=INDEX(CarType!$A$2:$A$5,MATCH(B4,CarType!$B$2:$B$5,0))
各セクションの内訳は次のとおりです
=INDEX( – “=”はセル内の数式の先頭を示し、INDEXは使用している(INDEX)Excel関数の最初の部分です。
CarType!$A$2:$A$5 – 取得するデータが含まれているシートCarTypeの列。(CarType)この例では 、各車種の(Car Model.)IDです。(ID)
MATCH（ –使用しているExcel関数の2番目の部分。
B4 –使用している検索テキストを含むセル（車種(Car Model)）。
CarType!$B$2:$B$5 – 検索テキストとの照合に使用するデータを含むシートCarTypeの列。(CarType)
0）） –検索テキストが一致する列のテキストと完全に一致する必要があることを示します（つまり、 CarType!$B$2:$B$5）。完全に一致するものが見つからない場合、数式は#N/Aを返します。
注：この関数「））」の最後にある二重閉じ括弧と、引数の間のコンマを覚えておいてください。(Note: remember the double closing bracket at the end of this function “))” and the commas between the arguments.)
個人的には、 VLOOKUP(VLOOKUP)から離れて、VLOOKUP以上のことができるため、INDEX-MATCHを使用しています。
INDEX-MATCH関数には、 VLOOKUPと比較して他の利点もあります。
- より高速な計算(Faster Calculations)
多くのVLOOKUP(VLOOKUP)関数が原因で計算自体に長い時間がかかる可能性がある大規模なデータセットで作業している場合、これらの数式をすべてINDEX-MATCHに置き換えると、全体的な計算がより高速になります。
- 相対列を数える必要はありません(No Need to Count Relative Columns)
参照テーブルに検索するキーテキストが列Cにあり、取得する必要のあるデータが列AQにある場合、 ( AQ)VLOOKUPを使用するときに、列Cと列AQの間にある列の数を知る/カウントする必要があります。。
INDEX-MATCH関数を使用 すると、データを取得する必要があるインデックス列（つまり列AQ）を直接選択し、照合する列（つまり列C）を選択できます。
- それはより複雑に見えます(It Looks More Complicated)
VLOOKUPは最近非常に一般的ですが、INDEX-MATCH関数を一緒に使用することを知っている人は多くありません。
INDEX-MATCH関数の長い文字列は、複雑で高度なExcel関数を処理する専門家のように見せるために役立ちます。楽しみ！
When to Use Index-Match Instead of VLOOKUP in Excel
For those of you who are well-versed in Excel, yоu are most likely very familiаr with the VLOOKUP function. The VLOOKUP function is used to find a value in a different cell based on some matching text within the same row.
If you are still new to VLOOKUP function, you can check out my previous post on how to use VLOOKUP in Excel.
As powerful as it is, VLOOKUP has a limitation on how the matching reference table needs to be structured in order for the formula to work.
This article will show you the limitation where VLOOKUP cannot be used and introduce another function in Excel called INDEX-MATCH that can solve the issue.
INDEX MATCH Excel Example
Using the following example Excel spreadsheet, we have a list of car owners name and the car name. In this example, we will be trying to grab the Car ID based on the Car Model listed under multiple owners as shown below:
On a separate sheet called CarType, we have a simple car database with the ID, Car Model and Color.
With this table setup, the VLOOKUP function can only work if the data that we want to retrieve is located on the column to the right of what we are trying to match (Car Model field).
In other words, with this table structure, since we are trying to match it based on the Car Model, the only information that we can get is Color (Not ID as the ID column is located to the left of the Car Model column.)
This is because with VLOOKUP, the lookup value must appear in the first column and the lookup columns have to be to the right. None of those conditions are met in our example.
The good news is, INDEX-MATCH will be able to help us in achieving this. In practice, this is actually combining two Excel functions that can work individually: INDEX function and MATCH function.
However, for the purpose of this article, we will only talk about the combination of the two with the aim of replicating the function of VLOOKUP.
The formula can seem to be a little bit long and intimidating at first. However, once you have used it several times, you will learn the syntax by heart.
This is the full formula in our example:
=INDEX(CarType!$A$2:$A$5,MATCH(B4,CarType!$B$2:$B$5,0))
Here is the breakdown for each section
=INDEX( – The “=” indicates the beginning of formula in the cell and INDEX is the first part of the Excel function that we are using.
CarType!$A$2:$A$5 – the columns on sheet CarType where the data we would like to retrieve is contained. In this example, the ID of each Car Model.
MATCH( – The second part of the Excel function that we are using.
B4 – The cell that contain search text that we are using (Car Model).
CarType!$B$2:$B$5 – The columns on sheet CarType with the data which we will use to match against the search text.
0)) – To indicate that the search text has to exactly match with the text in the matching column (i.e. CarType!$B$2:$B$5). If the exact match is not found, the formula returns #N/A.
Note: remember the double closing bracket at the end of this function “))” and the commas between the arguments.
Personally I have moved away from VLOOKUP and now use INDEX-MATCH as it is capable of doing more than VLOOKUP.
The INDEX-MATCH functions also have other benefits as compared to VLOOKUP:
- Faster Calculations
When we are working with large datasets where the calculation itself can take a long time due to many VLOOKUP functions, you will find that once you replace all of those formulas with INDEX-MATCH, the overall calculation will be compute faster.
- No Need to Count Relative Columns
If our reference table is having the key text that we want to search in column C and the data that we need to get is in column AQ, we will need to know/count how many columns are between column C and column AQ when using VLOOKUP.
With the INDEX-MATCH functions, we can directly select the index column (i.e column AQ) where we need to get the data and select the column to be matched (i.e. column C).
- It Looks More Complicated
VLOOKUP is quite common nowadays, but not many know about using the INDEX-MATCH functions together.
The longer string in INDEX-MATCH function help to make you look like an expert in handling complex and advanced Excel functions. Enjoy!
