VLookup vs IndexMatch – What To Use?

The VLOOKUP Formula

VLookup 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.

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.

This article will look at both methods and when to use VLookup vs Index Match.

Both Vlookup and Index Match are covered during our advanced Excel training.

Note: You can also used named ranges in Vlookup formulas


When To Use VLookup

VLookup is best used when you are trying to find items in your table or range by row. An example of this would be to find the price of an item in a warehouse – you could find it based on the ID of the item (a unique field). The following example will give you an example, providing a foundation so you know how to use it.


Using The VLookup Formula

When using a Vlookup to look for an precise 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).


Cell to be calculated from column


Building the Formula:

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.

Shows where to find the VLookup formula in a cell

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

Shows the requirements for a successful VLookup

Lookup Value             –      The value you are looking for – B13Table 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.


Shows the completed formula


The result:

Shows the result of the formula

Remember, the entry you are looking for must be in the first column of the table you are looking through and must be unique. 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.


Advantages And Disadvantages To VLookup


– A singular and simple formula to find values

– Easily finds connected data

– Can perform both exact and rough matches


– Tends to be very slow

– Can only find data in a column

– Cannot lookup values from the left


When To Use Index Match

Index Match allows for both horizontal and vertical lookups simultaneously. This is the key difference between VLookup and Index Match. However it is more complex to implement the Index Match combination.

While VLookup is great for searching for fields where you know the row, Index Match is much more flexible as you can search by row, or by column, or by both. In these situations its best to use Index Match instead of VLookup. The following examples will take you through how you can implement the Index Match formula.

Another great tool to have in your kit is using Random Numbers in Excel.


Using The Index 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.

Employee ID Column

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.


Highlights the relevant cells


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

Shows the index for F8

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

For more posts on Excel, read our guide on Macros in Excel here.


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 of the item you are looking for.

The match function returning the position of the items


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

Formula in B11 returning the row position of the item


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

Highlights the formula using Column E


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 Match Function the row number.


Screenshot using the index and match together

Array Value: The range you are extracting from  – E2:E10Row 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


Advantages And Disadvantages Of Index Match



– More flexibility, returns the value from any column (VLookup only returns from the right hand of the search)

– Index Match is much more reliable, as the return column stays the same even if more columns are added in between

– Takes less processing power and as such runs faster than VLookup


– More challenging to implement, and can be difficult to understand for people looking at your workbook



Overall as you can see, there are positives and negatives to both approaches for finding data. Generally speaking for less experienced users, VLookup will be a simpler formula to use. For more advanced Excel users I would recommend to use the Index Match over VLookup. Look at our handy guide on Excel Functions and Formulas if you want to learn more.

About Ben Richardson

Ben is a director of Acuity Training which he has been running for over 10 years.

He is a Natural Sciences graduate from the University of Cambridge and a qualified accountant with the ICAEW.

He previously worked as a venture capitalist and banker and so had extensive experience with Excel from building financial models before moving to learn SQL, Microsoft Power BI and other technologies more recently.