Do you struggle whilst trying to make complex searches in Excel? Do you not understand the rudiments on 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 is the most popular tool in 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.
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
- Array: This refers to the location or area from which you want to retrieve the value.
- 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.
- 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 score for Kratos in the Ibo language;
The image above depicts the basic lookup ability of the INDEX function. Although the VLOOKUP function can also perform this exact lookup, it has it’s limitations as it cannot lookup a single column nor lookup to the left as will be shown in the next example;
The above example depicts the retrieval of the Maths test score for Sekani. As illustrated, the INDEX function can look up a single column and retrieve the desired value. In this particular example, the column number which is an optional argument wasn’t required since we are working with a single column.
Getting the hang of the INDEX machine?…let’s explore it’s 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
- Lookup_value: This is referring to the value you want to search for. I.e This is the WHAT argument.
- 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.
- 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, equal to, or greater than the lookup_value.
The following example below depicts how this powerful and flexible function is used to retrieve the row number for Paula in the student column, I.e the position she occupies in that array;
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 column number for ENG, 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 is merit is that it allows you to search leftward of a given array. This is 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.
Here’s a simple guide to help you write the formula until you have practiced enough to help you memorize it;
In a nutshell, 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 test score of Williams in the IBO subject;
- INDEX needs numeric positions
- MATCH finds those positions
- MATCH is nested within INDEX
Inserting and deleting columns
If, for example, we were to insert a column between ENG and IBO, INDEX and MATCH still returns the correct result because our lookup arrays are separate.
Search to the left
In this example the subjects are placed on the left side of the student name column, this still won’t negate your lookup because INDEX and MATCH still works.
The INDEX MATCH combo function is so good that we didn’t even have to rewrite the formula, all we did was change the position of the columns.
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.
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. 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.
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 appreciate our readers and learners and would love to get your feedback.
If you enjoyed this article, don’t forget to like, share the article and subscribe for more exciting content.
To find out more about what we do, subscribe to our insights below. You can reach out to us directly for your consulting or training needs.
Receive Fresh, Weekly Analytics Content
Join 1K+ subscribers to receive original, freshly curated content every week.