Using VLOOKUP and INDEX Functions in MS Excel

In this tutorial I will explain the basic differences between VLOOKUP and INDEX functions in Excel, highlighting the strengths and weaknesses of each function.
The VLOOKUP function helps you to find data related to other information in a table's columns. Additionally, if you want to perform the same function horizontally, at the row level, you can use HLOOKUP.
The INDEX function returns the relevant data from a given range. It's basically doing the same thing but there are important differences that makes INDEX the preferred function instead of the lookup function category.
The syntax of the VLOOKUP function is the following:

• lookup_value — This parameter represents the value that you want to look up. The parameter is required to generate valid results, otherwise, the function will not work. This value must be in the first column of the range of cells selected for the table_array parameter.
• table_array — This parameter contains the range of cells in which the function will seek for the value inputted for the lookup_value parameter. 
• col_index_num — This parameter contains the number of the column, starting with 1 for the left-most column of the table. The selected column must contain the return value. 
• range_lookup — This parameter is option and have the role to force the function to look for an exact or an approximate match  of the lookup value, in the selected array. The parameter supports logical values such as TRUE or FALSE, and by default the TRUE value is selected.

Let's assume that we have the following set of data:

Supposing that I want to display the total amount sold on 10/14/15. To perform the VLOOKUP function, do the following:

1. Click outside of the table array.
2. Clickto display the Insert Function dialog box.
3. Select VLOOKUP function and click OK. The Function Arguments window will be displayed.
4. On the Lookup_Value field, write the date that we are looking for, that is 10/14/15.
5. On the Table_array field, select the table array. 
6. On the Col_index_num field, select the number of the column where the total amount is stored. 
7. On the Range_lookup field, select False to search for an exact match.

The result should look like in the following screenshot:

The issue with this function is that if you choose to move your table, including the data used by the function, the function will return a non-value. 

The index function has a simpler syntax, and can contain both VLOOKUP and HLOOKUP in just one function. The following parameters can be configured for INDEX function:

• array — This parameter contains the range of cells where the data can be found. If the array contains only one row, or column, that specific parameter, correspondent to the row or column, is optional. If the table contains more than one column or row, and only one parameter is used, depending if you want to return a value from either a column or a row, the function will return an range of the entire row or column in array.  
• Row_num — This optional parameter selects the row in array from which the value must be returned. If you don't use this parameter, the Column_num parameter is mandatory.
• Column_num — This optional parameter selects the column in array from which the value must be returned. If this parameter is not used, the Row_num parameter is mandatory.

Let's suppose that I want to display the total sales of the East region registered on 4/1/2014. To do this, follow the next steps:

Click outside of the table array.

1. Clickto display the Insert Function dialog box.
2. Select INDEX function and click OK. The Function Arguments window will be displayed.
3. On the Array field, select the entire table. 
4. On the Row_num field select the number of the row that corresponds to our criteria (East region and 4/1/2014). The number of the row is 7.
5. On the Column_num field select the column correspondent to the total sales, that is 7.
6. Click OK and check the results.

Thank you for viewing this tutorial. If you have suggestions of other tutorials related to other applications, don't hesitate to post a comment.