Excel Formulas: VLookup Vs Index & Match

The V Lookup formula is used to look up information in a list and extract the data into another list. It can also be used for matching; is the value I have in one list, also in another. One restriction with V Lookup is that the value you are looking for must be in the first column of the data you are looking through. VLookups can only extract data to the right of the column being interrogated.

The benefit of using Index and Match is that the data you are looking for can be in any column and you can extract data left or right of this column.

Both of these formulas are covered during our advanced Excel training course. If you are doing large amount of data management you may also find our article on turning a data table into a pivot table useful.

Using the VLookup Formula

When using a Vlookup to look for an exact match of data, the entry in the first column of the range you are looking through must be unique.   You can extract the data on the same sheet, to a new sheet or into a new document.

The following is a list of Personnel, in B13 we need to find information relating to EMP ID – 1169.   As we are looking for an exact piece of information, we have to specify this as part of the formula.

To indicate which column you are extracting from, you enter the numeric position of the column within the list, not the Column Label.    In the example below, we are going to extract the Salary, which would be column 7.   This value is calculated from the first column you are looking through, i.e. Column 1 (Emp ID).

Excel spreadsheet showing a table of data

 

Building the Formula:

Screenshot showing a vlookup formula being used in an Excel spreadsheet

In this example the formula would be built in  C13 to begin the formula enter an = sign, then type a V, all formulas beginning with V are displayed, double click on VLOOKUP to enter it.

 

Showing a Vlookup being constructed using the Excel formula builder

 

 

Four parts are required in the formula, the parts of the formula need to be separated by a comma.

Lookup Value             –      The value you are looking for – B13

Table Array                 –      The table you are looking through – B2:J10

Col Index                     –      The column number you are extracting – 7 (Salary)

Range Lookup             –      To explain you are looking for an exact match – FALSE

False is used in an Excel formula to indicate that an exact match is required.    This can also be entered as a numerical value 0.

Screenshot of a completed Vlookup formula in Excel ready to be used.

 

The result:

Result of using the Excel Vlookup on the Datatable

Remember, the entry you are looking for must be in the first column of the table you are looking through and must be unique.

Using Index with Match

The benefit of using these two formulas together, to extract data, is that the item you are looking for doesn’t need to be in the first column.   So let’s consider doing the same exercise as before looking for an employee ID, however, that entry is not in the first Column, and the data to extract, the Department, is to the left of the EMP ID column.

Datatable to use with Index And Match in Excel

First, we need to understand the Index and Match functions

Index Function

The Index Function returns the content of a cell within a specified range.

Example of using the Index function in Excel

 

The formula in F8 is being used to return the contents from the range A3:B5 – what is in the 3rd row and 2nd column of that range

Detailed explanation of using the Index function

Array: The range you are looking through – A3:B5

Row Num: The  row number of the item you are looking for within the range  – 3

Column Num: The column label of the item you are looking for within the range – 2

 

Match Function

The Match Function returns the position of an item within a single range.  So can be used with an Index Function to return the row number of the item you are looking for.

Examples of using the Match function

 

The formula in B11, is being used to return the row position of the Jacket in the range A6:A8.

An example of using the Match function in Excel

 

Lookup Value: The item you are looking for – A11

Lookup Array: The single range to look through = A6:A8

Match Type: To look for an exact match – 0 being used instead of FALSE

 

Using Index with Match

Example of Spreadsheet using Index with Match

So the INDEX function is used to highlight the Column you wish to extract the data from E2:E10 and the MATCH function is used to return the position of the item in B13 within the range G2:G10, thus giving the Index Function the row number.

Detailed screenshot of using the Index and match functions together.

Array Value: The range you are extracting from  – E2:E10

Row Number: The row number position– Match being used to find the row number.  (Finding the position of the item in B13 in the Emp ID col G2:G10)

Match Type: To look for an exact match – 0 being used instead of FALSE