Do you struggle whilst trying to make complex searches in Excel? Do you not understand the rudiments of how to use these powerful functions? This article will demystify the ambiguity surrounding these functions, and hence, make you more confident, and prolific in their usage.

INDEX and MATCH are amongst the most commonly used lookup functions in Microsoft Excel for performing more advanced lookups. This is because INDEX and MATCH are incredibly flexible – you can do horizontal and vertical lookups, 2-way lookups, left lookups, case-sensitive lookups, and even lookups based on multiple criteria. If you want to improve your Excel skills, INDEX and MATCH should be on your list.

This article explains in simple terms how to use INDEX and MATCH together to perform lookups. It takes a step-by-step approach, first explaining INDEX, then MATCH, then showing you how to combine the two functions together to create a dynamic two-way lookup.

To follow along with the tutorial, click HERE to download the workbook.

Let’s get started…

Understanding the INDEX function

The INDEX function = spreadsheet GPS

When you think of the Index function, consider it as sort of a GPS. I.e. it serves as a locator, it helps you find what you are searching for. It retrieves the value at a given location in a range. It returns a cell’s value based on the intersection of the row and column number of a range.

A guide for the INDEX function inputs

1. Array: This refers to the location or area from which you want to retrieve the value.
2. Row number: This has to do with the coordinates. I.e. the particular row number the value sits in. It tells INDEX which row to look in.
3. Column number: Though this argument is optional ( due to the square brackets [ ] surrounding it), it also refers to the coordinates I.e. the exact number of the column the value sits in. It tells INDEX which column to look in.

The example below depicts how this powerful and flexible function is used to get the City for Kratos;

More examples using the INDEX function

The image above depicts the basic lookup ability of the INDEX function. Although the VLOOKUP function can also perform this exact lookup, it has its limitations as it cannot lookup a single column nor lookup to the left as will be shown in the next example; we want to retrieve the name of the Road user whose Crash ID is 20212075

More examples using the INDEX function

The INDEX function can look up a single column and retrieve the desired value.
Let us retrieve the name of the 4th Road User. In this particular example, the column number which is an optional argument isn’t required since we are working with a single column.

Getting the hang of the INDEX machine?…let’s explore its significant other, the MATCH function

Understanding the MATCH function

MATCH function = Position specialist

The MATCH function is similar to the VLOOKUP function. The difference between them is that the MATCH function returns the column or row number whilst the VLOOKUP function returns a value. I.e. the MATCH function returns the position of a given value in a range. Also, it only handles 1 single column or row.

A guide for the MATCH function inputs

1. Lookup_value: This is referring to the value you want to search for. I.e This is the WHAT argument.
2. Lookup_array: This is quite similar to the array argument of the INDEX function. I.e. this is the WHERE argument. The location of what you are searching for.
3. Match_type: This is an optional but recommended input because it tells the MATCH function how precise you want to be. It finds a value that is either less than, exact match, or greater than the value you want to match.

The following example below depicts how this powerful and flexible function is used to retrieve the row number for Lex in the Road User column, I.e the position he occupies in that array;

More examples using the MATCH function

Remember this function only works on a single array which can either be a single column(as seen above) or a row. Let’s examine its functionality in a row.

The following example below depicts how this powerful and flexible function is used to retrieve the row number for the Roadtype Arterial Road i.e. the position it occupies in that array;

As promised we have examined these separate functions individually to get a glimpse of their capabilities, now let’s combine them and discover their superior edge.

Merits of using INDEX and MATCH

It is imperative that we first examine the advantages of combining these two functions before showcasing how to use them. The two main advantages are;

1- Lookup to the Left

The first merit is that it allows you to search leftward of a given array. This is a unique feature as it gives you flexibility and control over your ability to retrieve data.

2- Separate lookup and return ranges

You do not need to reference the entire table in order to retrieve the value or values you want. You can specify a single column or array for both the lookup and return ranges.

Now, for the moment you’ve been waiting for. Drum roll, please…

Combining the INDEX and MATCH functions

How do we combine these two functions in a single formula? To write the formula combining the two, we use the MATCH function for the row_number or column_number argument of the INDEX function.

In a nutshell, the MATCH function serves as coordinates to the INDEX function map capabilities. You can use the metaphor that MATCH is the Robin to INDEX which is the Batman.

The following example depicts how a simple combination of these two functions is used to retrieve the city of the road user named Nevader;

INDEX & MATCH function

To summarize:

1. INDEX needs numeric positions
2. MATCH finds those positions
3. MATCH is nested within INDEX

Matching both Row and Column Numbers

You can also search for two separate values by using two or MATCH functions inside the INDEX function for both the row and column numbers.

Well done! At this stage of this article, you are an INDEX MATCH guru.

How to deal with the most common INDEX & MATCH error

The most common error you will probably see when combining INDEX and MATCH functions is the #REF error, which simply implies that something is off with your referencing in your formula.

This is usually caused when the array in INDEX is a different length from the array in the MATCH function.

In the image below, you can see that the MATCH range includes row 8, while the INDEX range only goes up to row 7. When the specified criteria can’t be found because of the misalignment, this will cause the formula to return an error that says, #REF.

To fix the error, you can simply expand the smaller range to match the larger one. In this case, we would change the INDEX range to end at cell D8 instead of D7.

INDEX MATCH will also return the #N/A error (Not Available) when a value is not found.

Conclusion

I hope you thoroughly enjoyed learning about the awesomeness and the perks of using these two functions to make your work seamless and enjoyable.

INDEX MATCH can be difficult to understand at first, but I encourage you to practice as much as you can, and you’ll have this formula committed to memory in no time.

We do hope that you found this tutorial article exciting and insightful, for more access to such quality content, kindly sign up to the Resa platform by clicking here.

Thanks for learning with Us!