Use INDEX and MATCH to extract data from COT
In this write-up, we are going to use INDEX and MATCH to extract the data from the COT report. At first glance, most Excel users would be quick to conclude that it is impossible to pick the numbers any details from the report without the use of a macro (aka VBA). The currency description is in one of the rows and the data related to the currency is located in another low below the description. Using VLOOKUP will not be able pick up the data because the currency description is not on the
same row as the data. For those who do not know macros, most would have resorted to the primitive and yet simple way of eyeballing at the data, copy and paste the data to another worksheet. This maual way of extracting data is tedious and prone to error. What I would have done is to use the MATCH formula to hunt for the currency description and then use the INDEX formula to pick up the data for the respective currencies.
MATCH is similar to VLOOKUP. The difference is MATCH formula returns the position of the currency description in the range. If we start from row 1, MATCH formula effectively returns the row number of the currency description. d instead of the value. VLOOKUP cannot be used in this case because the data we wanted is not exactly on the cell itself. It is a few rows below the currency description. Using the MATCH formula which returns the row number for the description, we can move down a few more rows to identify the location of the data. Let me explain further with the first currency, CANADIAN DOLLARS, in the COT report. A copy of the COT report can be downloaded here.
In the workbook, the COT report can be found in the COT worksheet. Create a new worksheet. In A2, type in the full description for the currency
“CANADIAN DOLLAR – CHICAGO MERCANTILE EXCHANGE (CONTRACTS OF CAD 100,000)”.
To find the row the description is located, we enter the following MATCH formula
=MATCH(A2,COT!$A$1:$A$200,0).
The formula will return 7 as the position where the description is found. Since we start the range at row 1, 7 can also be intepreted as the row number. But I do not want to copy the long string of text into A1 as it contains irrelvant information. So what can I do? I can add an asterix after A2 so that the MATCH formula can do a partial match for me. The formula should be entered as follow
=MATCH(A2&”*”,COT!$A$1:$A$200,0).
With the change in formula, I can use the shorter description “CANADIAN DOLLAR” with the formula.
The COT report imported into Excel is organised in such a way that the data we wanted is always 3 rows below the currency description. We can find that row simply by adding 3 at the end of our MATCH formula
=MATCH(A2&”*”,COT!$A$1:$A$200,0)+3.
With the row identified, we can insert the MATCH formula into an INDEX formula which is a great formula to return the value displayed in the cell.
The formula is =INDEX(COT!$A$1:$A$200,
MATCH(A2&”*”,COT!$A$1:$A$200,0)+3).
Using the same formula but different currency descriptions in column A, you can extract the respective data into column B.
New! Comments
Have your say about what you just read! Leave me a comment in the box below.
Share this page:
Enjoy this page? Please pay it forward. Here’s how…
Would you prefer to share this page with others by linking to it?
- Click on the HTML link code below.
- Copy and paste it, adding a note of your own, into your blog, a Web page, forums, a blog comment, your Facebook account, or anywhere that someone would find this page valuable.
<a href=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a><a href=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a>
Excel Courses for Business Professionals
Copyright © advanced-excel.com 2007 – 2019. All Rights Reserved. Privacy Policy
Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
advanced-excel.com is in no way associated with Microsoft
