**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 **formula**s 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.

**FREE Excel Assessment Test PDF**

**Download ****Free ****Excel Assessment Test PDF EBook** - 34 page work book with 10 Sample Questions from the real **Excel Pre-Employment Exam**. Answers and explanations included!!!

**INDEX**(**array**, **row**_num, [**column**_num])

The syntax of **INDEX** comprises of three required parameters.

It’s a*Array*-**Range**of**cell**s 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 argument specifies the number of Row you want the*row_num*-**formula**to locate the value from the specified**array**. If the**array**range contains only a single**row**then this argument is optional.argument specifies the number of columns you want the*column_num*- Column**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.

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

There could be three types of *MATCH**_type.*

**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.**0**: Finds the exact**MATCH**and this is the argument we are going to be using most of times in our**formula**.**-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 **MATCH**ing 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

**cell**s 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 **MATCH**ing 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 **column**s 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 **column**s in between **lookup** **column** and results **column**, it becomes incorrect.

While working with **INDEX** and **MATCH** we specify **lookup** and results **column**s in **cell** references so when we delete or add any **column**s, 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 **formula**s 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.

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

**Answer**: The first answer is wrong cause the **Matchup** and **lookup** values are reversed we want to **INDEX** values from **column** C and **MATCH** from A and the second option is incorrect cause the ranges in the **INDEX** and **MATCH** are mismatched and that would not give us the correct answer and the third answer is the correct one.

### 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”.

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

**Answer: **The correct answer is the first one. The second answer is incorrect cause we have a mis**MATCH** in the ranges and it will not correct and the third answer is incorrect because we have **lookup** and results **array**s swapped.

### Exercise Question 3

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

- You can
**lookup**only right to the**MATCH****column** - You can’t do
**rows****lookup**with**INDEX**and**MATCH** - You can do an
**INDEX**and**MATCH**with Mismatched range in**INDEX**and**MATCH****array**s. - None of the above

**Answer:** None of the above are correct.

### [PDF Download] Advanced Excel Questions and Answers PDF EBook

### 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**

Questions**Pivot Table**- 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*

- Most frequently used
*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*

### About the Author: Vadim Mikhailenko, MBA

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.

Download FREE Excel Assessment Test PDF

**Download Free**
**Excel Assessment PDF EBook** - 34 page work book with 10 Sample Questions from the real **Excel Pre-Employment Exam**. **Answers and explanations included!!!**