by Matthew Kuo on February 6, 2013

in Database Theory, Excel

To learn more about Excel, go to the organized listing of all my Excel tutorial posts or review the

VLOOKUP MATCH is one of several possible lookup formulas within Microsoft Excel.  This tutorial assumes you already have a decent understanding of how to use VLOOKUP.  If you do not, please click here for a beginner’s tutorial on VLOOKUP.



VLOOKUP MATCH is an improved variation of your basic VLOOKUP or formula.  Using VLOOKUP MATCH allows you to perform a matrix lookup – instead of just looking up a vertical value, the MATCH portion of the formula turns your column reference into a dynamic horizontal lookup as well.  VLOOKUP MATCH is mainly useful for situations where you intended to perform heavy editing on your data set after you’ve finished writing your formula.  This is because VLOOKUP MATCH gives your lookup formula insertion immunity; whenever you insert or delete a column within your lookup array, your formula will still pull the correct number.  VLOOKUP MATCH is actually very similar to VLOOKUP HLOOKUP, but is slightly better because it does not require the creation of an additional row to label your column numbers.

The key difference between using VLOOKUP MATCH versus the basic VLOOKUP formula is that, in addition to your vertical lookup value (what you’ll be looking up down the left side of your table) you’ll also have a column lookup value (what you’ll be looking up across the top of your column headings).

The Syntax

VLOOKUP and MATCH are the two formulas that are combined to perform this lookup.  We’ll look at each of the formulas separately before putting them together.  The primary formula we’ll be using is VLOOKUP:

=VLOOKUP ( lookup value table_array , col_index_num [range_lookup] )

To use this formula, you’ll need a lookup value and a table array.  (We’ll address the column index number later and since we are not performing a range lookup, we can leave that part of the syntax blank)  In the example below, the lookup value is ID number “5” and the table array is the green box surrounding cells B6:F14.


Next we have the MATCH formula:

=MATCH ( lookup value lookup_array , [match _type] )

The match formula returns a position number based on your lookup value’s location within the array you’ve selected.  To use this formula you’ll need both a lookup value and a lookup array.  (The match type parameter should be left blank – doing so tells Excel that we want an exact match).  In the example below, the lookup value we’ll be using is the State of “WA” and the lookup array is the orange box surrounding cells B6:F6.


Putting it Together

The key to VLOOKUP MATCH is that we are replacing the “column index number” syntax of VLOOKUP with the MATCH formula.  Perform this combination using the following steps:

Step 1: Start by typing your VLOOKUP formula as you normally would, inputting the proper lookup value and table array for your lookup; in this example the lookup value is ID number “5” and the table array is the green box surrounding cells B6:F14.


Step 2: When you get to the column index number input, instead of typing in a hard coded number, start typing in the MATCH formula


Step 3: For the MATCH formula’s lookup value, select the cell containing name of the column you want to return from; in this example we want to return a State, so we click on it


Step 4: For the MATCH formula’s lookup array, select the row headings of your table array; in this example it is the orange box surrounding cells B6:F6.


Step 5: Close off both your MATCH formula and your VLOOKUP formula with two parentheses (doing this simply confirms for Excel that we want an exact match for the MATCH formula and that we don’t want to use a range lookup for the VLOOKUP)


How it Works

The MATCH formula we created returns the value 4.  Therefore, based on how we arranged the syntax, the VLOOKUP MATCH in this state is basically performing the same function as a VLOOKUP with a column index number of 4.


However, the key difference is that this column reference is now dynamic.  If I insert or delete a column from my lookup table, my return value will stay the same.  See below for an example of the difference in return values between VLOOKUP and VLOOKUP MATCH after inserting a column.


After the insertion occurs, the VLOOKUP formula’s column reference remains 4 and is now pulling from the City field.  Your return value has changed from “WA” to “Seattle.”  However, with VLOOKUP MATCH, since you’ve indicated by name which column you want to pull from, the column reference automatically updates and therefore you maintain the “WA” return value.


While VLOOKUP MATCH is clearly an improvement over the basic VLOOKUP, there are still drawbacks to using this formula.  With VLOOKUP MATCH, every lookup must still start from left to right.  This can become problematic if you want to append lookup keys to the right of your dataset.  Additionally, your return values are limited to the originally table array you’ve selected.  For example, if you were to append one or two columns to the right of your data set, you wouldn’t be able to lookup and return values from those columns without adjusting your table array.

If you want to use a matrix lookup formula combination without these specific limitations, consider using INDEX MATCH MATCH.

{ 13 comments… read them below or add one }

June 12, 2013 at 5:52 am



Richard June 24, 2013 at 10:33 am

Your website is very informative. I feel like I’m close to getting the result I need but not there yet despite the index match. Hypothetically using your example above, how would you pull the information of the header instead of the state “WA” if you had the starting point of ID 5 and WA? I have 2 different spreadsheets. I need to match the dates and name of a person in one spreadsheet to lookup where they were assigned to work on another spreadsheet. Not sure if it makes sense but any help you could give would be great. I had never heard of index match before reading your post about it versus vlookup.


Robert August 27, 2013 at 12:29 pm

Depending on what you’re looking up, you could take it 1 step further if the user desires. In your match example you reference “E6” (“State”). You could reference “C2” instead and have a data validation list in “C2” referencing your column headers. That way, the user can decide if they want to lookup the State, City, or anything else. I like this method though so it’s more dynamic when inserting columns. Thanks!


Alex November 25, 2014 at 5:55 pm

Great website!


Morton Wakeland February 16, 2015 at 7:21 pm

Well, the VLOOKUP + Match works – somewhat. I have columns headings A, B, C, D, and E. I have rows 1, 2, 3, 4, 5.
Information is contained within the matrix of these column/row headings. The odd thing is, all the lookup+matches work EXCEPT FOR MATCHING COLUMN A – I’m stumped.
The lookup occurs in a table on one worksheet, and the lookup table is on another. I’ve checked for unseen spaces – none. Every combination works except of I try to Match A.
Any thoughts most appreciated.

Well written by the way – thanks.
Mort Wakeland
Dallas, TX


Gen April 19, 2015 at 10:59 pm

Hi there, your website is very helpful, thank you. What excel formula would I use to match keywords in one column to their respective landing pages in the other column. Ideally all the keywords are within the landing page, but use different subfolders, so I can’t just use a concatenation, I need something else instead. Thank you in advance.


Ruth Udoka April 20, 2015 at 5:58 am

I find your write up informative.Pls I need help to go further.I want to use vlookupmatch to reference between two worksheets and highlight or format the worksheet cells that returns value.How do I do that.i DONT WANT TO DO THE FORMATING MANUALLY BECAUSE IT COULD TAKE WEEKS TO FINISH.


Zenon Coomer July 19, 2015 at 9:10 pm

I have been looking for a great website for excel and I have found it. Thank you


Wadood December 7, 2015 at 8:58 pm

How to get the results 0 instead of #N/A if the data is not found?


Matthew Kuo December 8, 2015 at 5:18 am

Hi Wadood,

Below is a tutorial to mask VLOOKUP errors:



Leena June 28, 2016 at 12:23 pm

Hi, Im very happy that i came across this tutorial.
Its very easy to understand with no confusions..

Thank you


saurav August 28, 2016 at 10:21 am

This is far by the most simple and elaborately lesson I learned. Thank you so much Sir.


Jayachandran C September 28, 2016 at 8:50 am

I have a excel with data I need to highlight sequence data for example,
Column A Column B
1 Sun
2 Mon
3 Tue
I need to find and highlight 1,2,3 with matching criteria (index)Sun, Mon, Tue on same order. Kindly let me know how is this can be done easily in excel



Leave a Comment

{ 3 trackbacks }

Previous post:

Next post:

Related pages

vlookup table arrayifs excel formulahow to display zeros in excelcalculator excel templateexcel stacked barhow to apply vlookup formula in excelvba excel remove duplicatesexcel template calendarexcel if isna vlookupinsert column in excel shortcutduplicate excel sheetexample of vlookup in excelhlookup excel formulaexcel delete duplicatesexcel if isna vlookuptick icon in wordexcel formulas if statementswhat if statements in excel 2010pasting in excelmicrosoft excel symbolremove duplicate excel 2010ucla femba acceptance ratetick on excel spreadsheetjohn isnaexcel random number generator normal distributioncountif on excelvlookup multiple criteriahighlight duplicates excel 2010excel ctrl hcountif greater than or equal tohow to remove duplicates from two columns in excelwhat is vlookupstry microsoft word freevlookup if statementexcel vlookup 2 valuesexcel function hlookupnested if statement in excel 2010bar and line chart in excelvba create a new worksheetbinning in excelbins in excelvba excel offsetvlookup syntaxvlookup on two columnsusing excel vlookupquarterly calendar template excelremove duplicates excel vbaucla graduate gymexcel countifexcel replace formulavlookup on two columnsalexa popularityif iserror vlookup formulairs tax liability calculatorexcel freeze formulaeasy vlookup stepsvlookup syntax in excelwhat does value error mean in excelhistogram chartshow to perform vlookup in excelif statements excellexcel logical formulahow do i create a column chart in excelcreating histogram in excelduplication formula in excelexcel if iserrorhow do i insert a tick in wordvlookup excel trainingword insert tickshort cuts for excelhow to forecast revenue in excelversion of a worksheet shows the results of formulasexcel convert horizontal to verticalh look upusing vlookup in excel 2007vlookup and hlookup in excel 2010iserror excel formulavlookup in two different sheets