VLOOKUP while being very useful is quite limited because of its certain limitations say you can’t lookup anything left of the identifier and more and while it does the job most of the times, it isn’t perfect so today let’s learn a powerful alternative to VLOOKUP and learn how it functions.

INDEX and MATCH – Why Use INDEX and MATCH Combo

INDEX and MATCH work by combining Excel’s two formulas which are INDEX and MATCH. Let’s have a basic look at their individual syntax and then we will jump right on to what we want to do with the combinations of these two.

INDEX Syntax

INDEX function returns a value from an array when we specify a column and row number.

INDEX(array, row_num, [column_num])

The syntax of INDEX comprises of three required parameters.

• Array- It’s a Range of cells which are going to be used for the Lookup.

A couple of things to note.

When you only select a column in the array range then the column argument is optional and similarly if you only select a row in the array then the row argument in the formula is option.

• row_num- Row argument specifies the number of Row you want the formula to locate the value from the specified array. If the array range contains only a single row then this argument is optional.
• column_num- Column argument specifies the number of columns you want the formula to locate the value from the specified array. If the array range contains only a single Column then this argument is optional.

Lets now look at the basic example with the syntax.

=INDEX(A1:D20,3,2)

Now, on the above example the formula is locating value from array A1:D20 and finding the value from the cell 3^rd row and 2^nd column and which will be the cell B3.

 

MATCH Syntax

MATCH function returns are the relative position of a Lookup value on a given array. Now that may sound confusing so let’s look at the syntax of the same and then understand it better with the help of an example.

MATCH(lookup_value, lookup_array, [MATCH_type])

The syntax of the MATCH function requires three argument.

• lookup_value- This argument is the value we are searching for and it could be text, number or logical or cell reference to the same and it’s a required argument.
• lookup_array - It’s a required argument and it’s the array in which we are searching for.
• MATCH_type- This argument defines whether we are searching for exact or nearest value.

There could be three types of MATCH_type.

1. 1 or omitted: Finds the largest value that is less than or equal to lookup_value and you must sort the array in ascending for this to work.
2. 0: Finds the exact MATCH and this is the argument we are going to be using most of times in our formula.
3. -1: finds the smallest value that is greater than or equal to the lookup_value In order for this work right you need to sort the array range in descending order.

 

Example for MATCH function

We have the data for top 20 websites in the world as of September 2019 and we want to find out the position of the 5^th rank website in our array so will use the formula =MATCH(5,A1:A21,0) and we are MATCHing the rank 5 with the help of the formula and that gives us the correct value.

 

Using INDEX and MATCH

Now we have seen how each of these function work individually we are now going to see them in action in the combination and put them to work. The way INDEX and MATCH functions is that we INDEX a value from an array of a column based on the row returned from the MATCH function.

Now if that seems confusing, I can understand where you’re coming from let’s have a look at the syntax of the INDEX and MATCH formula

INDEX (column to return a value from, MATCH (lookup value, column to look up against, 0))

Now let’s understand this syntax from the help of an example

 

Example INDEX & MATCH

In the above example we are looking up the number “1” rank website and getting the “Site” as the results, which is “Google.com”

So what we did s we defined a INDEX array and which is “C2:$C$21” and that is column we are going the return the value from and since we only defined a column then all we need a row value.

So, for that we are turning towards the MATCH function and on the MATCH function we have defined the MATCH value as 1 and since its numerical value we don’t need to enclose this in double inverted commas.

Now we define the lookup_array on our MATCH function which in the case of this example will be $A$2:$A$21 and so now our MATCH function will look for 1 from cells A2 to A21 and find us the relative number from A2 to A21 where it Matches and since in our example the position of value 1 in column A is 6^th row. But since we are getting results from the relative position starting from cell A2 we will get the results to our MATCH argument as 5 and then it feeds the value to our INDEX function and it returns the 5^th row from cell C2 as the value and which will get us the result we are looking for.

An Important thing to note here is that the INDEX array and MATCH array should be same size and start and end from the same rows otherwise the formula will give us incorrect results.

 

Using INDEX and MATCH as HLOOKUP

Now one of other thing you can do with INDEX and MATCH is lookup within rows as well don’t have to change anything within the formula, let’s look at an example and see this aspect of INDEX and MATCH in action.

In the above example you can see we have done rows lookup and we are getting values from row range $B$2:$K$2 by MATCHing the values 5 in row range $B$1:$K$1.

Why Don’t Just Use VLOOKUP: Benefits of INDEX and MATCH combo

Now when we learned this formula, I was wondering the same so let me list some benefits of using this formula over VLOOKUP and show you why I have switched over permanently from VLOOKUP to INDEX and MATCH.

Lookup Left or Right without worrying

One limitation of the VLOOKUP formula is that you always have to have the identifier as the first column and we can’t look up to right of the column and with the help of the INDEX and MATCH we can do that. Let’s have a look at an example of doing just that.

The formula used in the above example is =INDEX($A$2:$A$21,MATCH("Youtube",$B$2:$B$21,0))

In the above we are looking up the rank by the name of the site and we queried for the rank which resides on the left of the MATCH value and this not possible to do via VLOOKUP.

 

Insert or delete columns safely

When you specify a VLOOKUP range you have to hardcode the column to get the results from and which makes it so when you delete or insert any columns in between lookup column and results column, it becomes incorrect.

While working with INDEX and MATCH we specify lookup and results columns in cell references so when we delete or add any columns, Excel adjusts things by itself.

Higher Processing Speed

INDEX and MATCH has higher efficiency when compared with VLOOKUP and in small tables you won’t notice the difference however when you’re working with complex workbooks with a lot of formulas then you’d certainly notice the difference.

 

INDEX and MATCH Exercise questions

Exercise Question 1

Observe the image below and in the cell I1 what would the correct INDEX and MATCH formula for finding the site of the rank 2.

1. =INDEX(A2:$A$21,MATCH(2,$C$2:$C$21,0))
2. =INDEX(C1:$C$21,MATCH(2,$A$2:$A$21,0))
3. =INDEX(C2:$C$21,MATCH(2,$A$2:$A$21,0))
4. None of the above are correct.

Show Answer

Exercise Question 2

Have a look at the data in the image below and select the formula would grab us the rank based on the name of the site “Reddit”.

1. =INDEX($A$2:$A$21,MATCH("Reddit",$B$2:$B$21,0))
2. =INDEX($A$1:$A$21,MATCH("Reddit",$B$2:$B$21,0))
3. =INDEX($B$2:$B$21,MATCH("Reddit",$A$2:$A$21,0))
4. None of the above are correct.

Show Answer

 

Exercise Question 3

Which of the following statement is correct about the INDEX and MATCH formula?

1. You can lookup only right to the MATCH column
2. You can’t do rows lookup with INDEX and MATCH
3. You can do an INDEX and MATCH with Mismatched range in INDEX and MATCH arrays.
4. None of the above

 

Show Answer

Topics Covered in this EBook

• Advanced Excel Formulas and Function Questions
  • VLOOKUP Questions and Answers
  • LOOKUP
  • SUMIF, SUMIFS
  • HLOOKUP, LEFT, FIND, SEARCH, SUBSTITUTE
  • COUNT, COUNTA, COUNTIF, COUNTIFS
• Pivot Table Questions
  • Pivot Table setup and configuration
  • Dynamic ranges in Pivot Tables
  • Data Sorting and grouping in Pivot table
• Excel Keyboard Shortcuts Questions
  • Most frequently used Excel Shortcuts
• Strategies to Successfully Pass Excel Interview & Assessment Test
  • Preparation for the Exam. What do you need to know
  • Preparation for the day of test
  • Tips to manage your time during Assessment Test
  • Tricks for Answering Questions
    • Strategies to answer Multiple Choice Questions
    • Tips and Tricks to answer True/False Questions
    • How to succeed with Multiple Answer Questions
    • Simulator questions: everything you need to know

and much much more

 

Buy 

Vadim lives in Milwaukee, Wisconsin and works as an Information Technology Consultant, Coach and Educator. Vadim is very passionate about Milwaukee community and teaches classes to underprivileged students at local community college. Throughout his consulting career Vadim had to study a lot of tools and technologies and learned hiring process by helping organizations to hire candidates and supported students to get prepared and pass interview and assessment tests.

Vadim loves to share my knowledge with others and enjoys teaching people new skills to help them get hired for the dream job.