**The INDEX function in Excel returns a value according to its position in a table.**

## VLOOKUP vs INDEX

For many people, the difference between the functions VLOOKUP or INDEX is not obvious. In fact, the difference is quite simple.

**VLOOKUP searches by value**(employee name, product name, product code, ...)**INDEX searches by position**(for instance returns the value in column 2 and row 5)

**There is no difference between the two functions in terms of search speed**.

## Presentation of the function INDEX

The function INDEX works with three arguments

**Identify the range of cells containing the data you want to reference**This range can be a single cell, a row, a column, or a block of cells

**Determine the row position of the value you want to return.**The row position is the number of the row containing the value, starting with 1 for the top row.

**Determine the column position of the value you want to return.**The column position is the number of the column containing the value

**The order of parameters is very important.** Often, users reverse the setting of the row and the column and inevitably, the function does not return the correct result.

## Example: INDEX to return the month name

- Let's say, you have the list of the months' names on a worksheet.
- What is month #6?
- Well, with the function INDEX, it's easy to find the answer.

=INDEX(A2:A13,6)

But of course, **we can replace the value 6** with the reference of a cell, like this

=INDEX(A2:A13,C1)

Now, If the data source is in a row, the writing of the function is a little bit different.

- The second argument is = 1 (search on the first row)
- The third argument is the position

=INDEX(C3:I3,1,D8)

## Function to return the position

So, **INDEX expects a rank to return a value in your reference table. But if you have a huge list of values on** your table, like customers' names, how to find the position of the clients Smith or Garcia?

This is where the function MATCH will help you to convert a value to a position 😉

Here, for instance, we select a month and the function MATCH returns the rank.

=MATCH(E1,C1:C7)

## Function INDEX with 2 dimensions

**The INDEX function is very powerful with a 2-dimensional array**. Like that, you can return the value of a cell inside a grid of data. This is how most Excel users use this function.

For example, we want to return the distance between 2 cities (the explanation to build such a cross table in this article)

So here, we must convert the name of the cities as a position with the** function MATCH**

=MATCH(B10,A2:A8,0)

Then, we will use this information in the **INDEX function** to return the distance between the cities

=INDEX(B2:H8,C10,C11)

**It is compulsory** that your selection does **NOT include the headers.**