Indexes in Programming

“Index” is a common term used in all programming languages. It basically acts like a serial number of which an item or value can be referenced. For example, in the picture below, “Great Wall of China” is the 5^th item in the series. In programming we say that its index is “5.” In some programming languages the index numbering starts with “0.” If such is the case, the index of the same “Great Wall of China” would be “4” (0, 1 , 2, 3, 4). 

In general, “index” can be used to reference the values of an array or a range (in Excel).

The Index Formula in Excel

In MS Excel, index is a formula that helps us find the value in a specified array.

Syntax of the formula:

Index(< array name >, < row number >, [< col number>])

Where

1. Array is an array of values (a list of one or more dimensions)/ a reference to an array.
2. Row number is the row index being referred to
3. Col number is the column index being referred to

Example 1

In this example we are trying to find the value in the 7th position of the lookup range (the parameter used in the formula).

Here the range is B2 to B9 i.e. Starting from the value “Taj Mahal” (position 1) in B2 until “Great Pyramid of Giza” (position 8) in B9. Visually we can see that Petra is the 7^th value in the selected range. Hence, it is the value returned by the formula.

Note: S.no is given in the example for easy understanding and it has nothing to do with the Index formula.

Example 2

Let us try to find the price of the 7^th item (“Sambar Idly”) sold at a snack bar. There are two columns here, one with the list of items and the other with the respective price. 

Note: Paste this table starting with cell A1 on the Excel sheet for the formula’s range to work.

Item	Price
Ice cream	5
Sweet corn	6
Spinach Candy	8
Banana Cake	3
Peas Pulao	40
Methi Roti	30
Sambar Idly with ghee	20
Veg clear Soup	40
Masala Papad	10

The formula would be  =INDEX(A2:B10,7,2)

Where 

A2:B10 is the range of data in the table

7 because we are looking for the 7^th item (row-wise)

2 because we want the price of the item. The price is in the 2^nd col. And the answer would be 20.

The Index Function in VBA

The Index function in VBA is very simple. Just as in the formula, here we also have to pass the array and the position as parameters. The function then returns the value in that position.

Example 1

In the example below, there is an array of students in a class. Let us assume that we know the roll number of the student who has obtained Rank 1. We are asked to find his/her name.

Sub index_demo()

' declare variables
Dim arr_students()
Dim first_rank

' initialize the arrayarr_students = Array("John Capps", "Michel Pinny", "Kanya Dhan", "Ron Dincy", "Bella", "Mayur", "Jack finner", "Emi Thinner")

' We know that the fourth student in the array has got the first rank. We need to find his name
first_rank = Application.WorksheetFunction.Index(arr_students, 4)

'Display the name of the first rank holder in a messagebox
MsgBox first_rank & " is the student who got the first rank."

End Sub

Example explained:

To start with, we have declared the required variables. Then we have initiated an array (arr_students) with the list of student names as values. 

In order to use the Index function, we should have the array and required position number as parameters. As we have created the array already (arr_students) and we know that the name of the student with the roll number “4” is to be fetched, we proceed to use the function.

Application.WorksheetFunction.Index (arr_students, 4)

Application refers to the Excel application being used currently.

Worksheetfunction refers to the bunch of functions offered by Excel for any worksheet.

Index is also a worksheetfunction which we are learning to use here.

The output/return value of this function is passed/caught in the variable declared earlier — first_rank.

Finally, the value of the variable is displayed with a description in a message box.

Example 2

In this example, we will use the worksheetfunction index in VBA but the array that we refer to will be a Range of Excel cells.

Scenario: Some sports personalities ran a race and reached the finish line in a particular order. The array of players is in the finishing order. We are asked to find the runner of the race.

Let’s try to find the person who wins the second place in the competition. This will be our position value in the function.

The range of the array is Range(“B2:B9”).

Knowing the required values, we can proceed with writing the code.

Sub index_demo2()

' declare variables
Dim arr_players()
Dim runner
' initialize the array
arr_players = Range("B2:C9")

' We know that the second player in the array is the runner. We need to find his name
runner = Application.WorksheetFunction.Index(arr_players, 2)

'Display the name of the runner in a messagebox
MsgBox runner(1) & " is the player who won the second place. His email id is " & runner(2)

End Sub 

Code explained:

As mentioned in Example 1, we have declared and initialized the variables. Then we use the Index function to find runner (2^nd place) of the match. 

The flow of the program is the same with the difference that here we have been forced to use runner(1) to display value. 

Reason: An Excel range is considered a multidimensional array. Because of this, the return value is not just a string. Instead, it is also an array. To find a value in that array, we have to specify index. 

(1) is used here since there is only one value (or we have specified only 1 column in the range).
To understand it better, if we had used the range Range (“B2:C9” ) in our code, then the return value of the index would have both values in Range (“B2”) and Range(“C2”) i.e. All values of (various columns) of the second row (position parameter given in the Index function).

The image above clearly shows the value of the runner object (the returned value). It has both the name and the email address. Suppose, I want to display the email address of the runner, I will have to display runner(2) in the messagebox.

MsgBox runner(1) & " is the player who won the second place. His email id is " & runner(2)

<!-- /wp:shortcode -->

<!-- wp:image {"id":14452,"sizeSlug":"large","linkDestination":"none"} -->
<figure class="wp-block-image size-large"><img src="https://software-solutions-online.com/wp-content/uploads/2021/06/unnamed-11.png" alt="Excel message box that reads "Fred is the player who won the second place. His email id is [email protected]"" class="wp-image-14452"/></figure>
<!-- /wp:image -->

<!-- wp:heading {"level":3} -->
<h3>Example 3</h3>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>In the example below, we get the capital of a state whose index is known. So, we are using an array of two dimensions. If we use formula, we refer to the 4<sup>th</sup> row and 2<sup>nd</sup> column of the selected range. So the value of the cell turns out to be “Patna”.</p>
<!-- /wp:paragraph -->

<!-- wp:image {"id":14457,"sizeSlug":"large","linkDestination":"none"} -->
<figure class="wp-block-image size-large"><img src="https://software-solutions-online.com/wp-content/uploads/2021/06/unnamed-12-1024x266.png" alt="Example of using array to find capital states." class="wp-image-14457"/></figure>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>Let us do the same using VBA.</p>
<!-- /wp:paragraph -->

<!-- wp:shortcode -->

Sub IndexMatch_demo1()

' declare variables
Dim required_pos, str_capital, arr_states_cap, capital_col

' initialize variables
required_pos = 4
capital_col = 2
arr_states_cap = Range("B2:C16")

'use the function to get the capital
str_capital = Application.WorksheetFunction.Index([arr_states_cap], required_pos, 2)

' display the result
MsgBox str_capital

End Sub

Code explained:

Post declaration and initialization of variables, the Index function is used to get the value  in the 4^th row, 2^nd column of the selected state, capital range of cells on the Excel sheet.

Finally, the output that we see in the message box would be “Patna” which is the capital of Bihar.

The Match Formula and Function 

Here is my article about the Match formula in Excel and Match function in VBA:

Using the Match Function in VBA and Excel – VBA and VB.Net Tutorials, Education and Programming Services (software-solutions-online.com)

Using the Index Function with the Match Function

As we have already understood, the Index function gives the value in the known index of an array. The Match function does the opposite. It provides the index value of an item in an array or range of values.

It is possible to use one of these functions within the other. i.e. Match within Index (or) Index within Match.

Example 1

Here is a table that has details of States, Capitals and another column to describe when it was formed (“Founded on”). We are asked to find the “Founded on” value of the state “Maharashtra”.

Let us assume that we do not know its index. So, initially we will use the Match function to find its index.

Once we get that index, using Index we can get the respective values of that index (rows) against all columns.

Sub IndexMatch_demo()

'declare variables
Dim strstate, arr_states, arr_capitals, arr_founded, rowposition_state, str_capital, str_founded

' initialize variables ( set values for them )
strstate = "Maharashtra"
arr_states = Range("B2:B30")
arr_capitals = Range("C2:C30")
arr_founded = Range("D2:D30")

' find the position of the state in the first array ( 0 here states an exact match)
rowposition_state = Application.WorksheetFunction.Match(strstate, ([arr_states]), 0)

'Find the respective capital and "founded on" values of the said state.
str_capital = Application.WorksheetFunction.Index([arr_capitals], rowposition_state)
str_founded = Application.WorksheetFunction.Index([arr_founded], rowposition_state)

' display all values
Debug.Print str_capital(1)
Debug.Print str_founded(1)

End Sub

Code explained:

The required variables are first declared and then initialized. From these lines, we infer that we are supposed to find the capital and “Founded on” values of  the state “Maharashtra.” So, using the Match function in VBA, we find the position of  the state ”Maharashtra.” Then using the same value as the index, we find the capital and “Founded on” values from the respective arrays and display the same in the Immediate window (using the debug.print statement).

In order to explain this further, three arrays were created in the example. If the ranges quoted for each of these arrays are not within the same range ( row values), then this concept of using the same index (row num here) for finding values in other arrays will not work.  So, let us redo the same with one range as a subset of another range to understand the concept better.

Sub IndexMatch_demo3()

'declare variables
Dim strstate, arr_states, start_pos, end_pos, rowposition_state, str_capital, arr_states_captials

' initialize variables ( set values for them )
strstate = "Maharashtra"
start_pos = 2
end_pos = 30

'dynamic defining of the arrays from the excel sheet. The rows values are not hardcoded here.
arr_states_captials = Range("B" & start_pos & ":D" & end_pos)
arr_states = Range("B" & start_pos & ":B" & end_pos)

' find the position of the state in the states array ( 0 here states an exact match)
rowposition_state = Application.WorksheetFunction.Match(strstate, [arr_states], 0)

'Find the respective capital and "founded on" values of the said state.
str_capital = Application.WorksheetFunction.Index([arr_states_captials], rowposition_state, 2)
str_founded = Application.WorksheetFunction.Index([arr_states_captials], rowposition_state, 3)

' display all values
Debug.Print str_capital
Debug.Print str_founded

End Sub

Code explained:

The difference in this modified code lies in the dynamic building of array range. A single dimensional array is defined in order to find the index using the Match function. Then we find the respective values from the other two columns. The Index function here uses both the row position and col position parameter since the array is multidimensional (a complete picture of this is available in the image above).

Conclusion

In my experience, I see that the Index function can be used with multidimensional arrays but when it comes to the Match function, multidimensional arrays have to be sliced using loops and conditions before being passed as parameters. Using a combination of both these functions (Index and Match), it is possible and easy to point to any value/index/any target in a table or an array or a range of Excel cells. Using them wisely can produce great results.