How to use INDEX and MATCH in Excel — example

You can use the INDEX and MATCH functions in Excel if you need to find and match the information in two tables. The combination of functions is used to retrieve the data based on the values of your particular cells. I will show you how to implement such kind of calculation.

Imagine you invest your money in stocks with a US-based broker. Now you want to prepare a European tax report with all your transactions converted with the corresponding euro FOREX rate for the past year.

Finding FX Rate according to the date of the transaction

Every transaction will have its date and the FOREX (FX) rate that will be different for each day. Assume you don’t have that information in your initial table by default.

So, you upload the rates from the European Central Bank website and add this information to your table. The question is now — how can you retrieve the corresponding rates to match the dates of your transactions from table 2?

Table 1. Data about transactions

Table 2. FX Rates by dates. Source — the European Central Bank website.

The first thought that could come to your mind is to use the VLOOKUP function. The function looks like this:

=VLOOKUP(H2,B3:F25,3,FALSE)

Where

• H2 = a cell where the information will be placed
• B3:F25 = the range of data you want to search
• 3 = column index number
• False = exact match.

Obviously, you are saying Excel — here is an FX rate on your left column of the table, now look through this range of cells and find the dates in the ‘Date’ column. The problem is that you have two columns with dates in two different spreadsheets. This means VLOOKUP will not work.

A combination of INDEX and MATCH functions will be the right choice in this case. The purpose of the INDEX function is to search for specific information in the table. At the same time, MATCH will return you the rown number and act as one of the attributes of the INDEX function.

STEP 1. Create INDEX function

There are two possible ways to implement INDEX function

1. with array

=INDEX(‘array’;row_num;[column_num]

2. with reference. Since we need to choose reference from the second table we are using the second one.

=INDEX(‘reference’;row_num;[column_num;[area_num])

So, our reference is a range of information in columns and rows in another table ‘FX Rates’!$A$1:$B$259. We will lock this array of cells with dollar signs so it remains fixed while we extend the formula across the list of rows with transactions. For instance, we want to find XT Rate in the second table in the second row for 02.01.2020. Our column number and row number will be 2 and 2.

=INDEX(‘FX Rates’!$A$1:$B$259;2;2)

Where

• ‘FX Rates’!$A$1:$B$259 — range in Table 1
• 2 — column number
• 2 — row number

Identify FX Rate in Table 2

The results in Table 1

STEP 2. Create MATCH function

MATCH(Vlookup_value; Vlookup_array; [match_type])

The MATCH function allows us to retrieve a number of a particular row for a specific date in the table. For example, if we are looking for the date 24.08.2020 in Table 2, we can use

=MATCH(C5; ‘FX Rates’!$A$1:$A$259; 1)

Where

• C5 — column in Table 1
• ‘FX Rates’!$A$1:$A$259 — range in Table 2
• 1 — less then

Identify row position with MATCH function

Knowing these two factors — the number of a row and FX rate we can quickly retrieve necessary data.

STEP 3. Create INDEX MATCH function

The formula will be like this

=INDEX(‘FX Rates’!$A$1:$B$259; MATCH(C5; ‘FX Rates’!$A$1:$A$259; 1); 2)

FX Rate in Table 1 from Table 2 be date

Let’s check. Table 1 and Table 2.

Table 2

Table 1

If the formula does not work correctly, you can troubleshoot the two most common mistakes:

• Depending on your OS localization, try using a semicolon instead of a comma mark to separate the arguments in the formula, and vice versa
• Try altering the cell format. For example, make it a ‘Number’, instead of ‘General’.

Thank you for reading.