How to Use Index and Match to Replace Vlookup

Check out the video below ​to learn how to ​use Match and Index to replace Vlookup in Excel.

Video

play

Video summary

We're going to look at replacing Vlookup with the Index and Match functions.

Before we do that though, it is important to note why we would. What is wrong with Vlookup that it needs replacing?

The short answer is that Vlookup ​cannot ​look to the left. Let us explain: ​suppose we look up a value with Vlookup. This value is in column 1. We can then only return a value that is to the right, in column 2, 3, 4, etc. It's not possible to return a value from column 0, -1, -2 or any other column to the left of the column of the lookup value.

But you can do this with Index and Match (check out their syntax below).

Match can do the searching. It searches a cell range and returns the row of the value it found.

Index can do the returning of the value. ​It returns a value from a cell range given a row number.

​So Match can look up a value in one cell range, get the row index and pass it to the Index function. The Index function can look at a different cell range (with values that we want to return) and return the value at that specific row.

That would look something like this:

=INDEX(​Return cell range​, MATCH(​​Lookup cell range​, 0))

The big upside to this is that the ​Return cell range​ can be anywhere you want. ​Including to the left of the ​Lookup cell range​.

You can try it out for yourself using the Excel file posted below the video!

​​Syntax of Index

=INDEX(Array, Row number, [Column number], [Area number])

Argument 
Argument description
1Array One or more arrays or cell ranges.
2Row numberThe row of the value we want to return.
3Column number (Optional)The column of the value we want to return.
4Area number (Optional)The array index of the value we want to return.
Only useful when you have more than 1 array as the first argument.

More information about the Index function here.


Syntax of Match

=MATCH(Lookup value, Lookup array, [Match type])

Argument 
Argument description
1Lookup valueThe value to search for.
2Lookup arrayAn array or cell range to search in.
3Match type (optional)Either -1, 0 or 1.
1, smaller than (default): finds values equal to or smaller than the Lookup value.
0, exact match: only finds the exact Lookup value given.
-1, greater than: finds values equal to or greater than the Lookup value.

More information about the Match function here.



You may also like

Is there a ‘minus sum’ for spreadsheets?

7 Excel Tricks for Marketers

  • […] The solution that most people use is really more of a workaround. The idea is that you combine the Index and the Match function to each do a part of what Vlookup would normally do: Match can find the value that we’re looking for and return a position and Index can use that position to return a value in a different column. This works and it makes you appreciate how you can solve problems in many ways in Excel. We’ve even created a video explaining this solution. […]

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
    __CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"36c04":{"name":"Main Accent","parent":-1},"3a8fd":{"name":"Accent Light","parent":"36c04","lock":{"saturation":1,"lightness":1}}},"gradients":[]},"palettes":[{"name":"Default","value":{"colors":{"36c04":{"val":"rgb(255, 204, 102)","hsl":{"h":40,"s":1,"l":0.7}},"3a8fd":{"val":"rgb(241, 241, 241)","hsl_parent_dependency":{"h":0,"s":0,"l":0.94}}},"gradients":[]},"original":{"colors":{"36c04":{"val":"rgb(255, 204, 102)","hsl":{"h":40,"s":1,"l":0.7}},"3a8fd":{"val":"rgb(241, 241, 241)","hsl_parent_dependency":{"h":0,"s":0,"l":0.94}}},"gradients":[]}}]}__CONFIG_colors_palette__

    ​Get Good at Excel on Autopilot

    Sign up to our newsletter and receive ​Excel articles, tips and tricks delivered straight to your inbox. All you have to do is read them! ​Plus, to get you started, you'll receive a PDF with 200+ Excel shortcuts.

    __CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"2dd0d":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"2dd0d":{"val":"rgb(45, 164, 92)","hsl":{"h":143,"s":0.57,"l":0.41}}},"gradients":[]},"original":{"colors":{"2dd0d":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
    Join List
    >