The Ultimate Guide to VLOOKUP

by Matthew Kuo on November 7, 2015

in Database Theory, Excel

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

Learning the VLOOKUP formula is, for most people, the first step taken towards becoming an advanced Excel user.  For me, this milestone actually occurred in 2004 during an internship at Microsoft in the company’s Commercial Operations Division.  One of the senior analysts showed me the ropes by teaching me the formula.  While I was still getting familiar with spreadsheets in general, learning that formula was definitely the first time I started to understand the true power of Excel.


VLOOKUP is by no means perfect, but the formula has stood the test of time because to its usefulness and intuitiveness.  Even though most Excel pundits agree that INDEX MATCH is better than VLOOKUP, VLOOKUP is still several times more popular than any Excel formula out there, according to Google Trends.

As a new analyst, being asked to find a small piece of information from a larger data set is probably one of the first things you’ll be asked to do.  And 95% of the time, the optimal tool for this task will be Microsoft Excel.  If you’re brand new to spreadsheets, you’re probably not going to start with a formula combination like .  VLOOKUP is much easier to learn if you’re new to concepts like formula sytnax and lookup tables, and by starting with VLOOKUP, you’re much less likely to make errors.

Below is a guide to show you everything you need to know about the VLOOKUP formula.  If you’re still learning how to use Excel formulas, start with the basics to ground yourself in the foundational concepts.  Then you can move on to formula combinations and the more advanced techniques related to VLOOKUP, including using INDEX MATCH as an alternative.  And finally, if you’re having issues with VLOOKUP, check out the troubleshooting section.  This guide will be updated periodically with new content.


Resources & Guides

VLOOKUP Formula Combinations

VLOOKUP Techniques

VLOOKUP Alternatives

VLOOKUP Troubleshooting

{ 4 comments… read them below or add one }

Manette Kidney November 8, 2015 at 12:27 pm

Below is the formula I’m currently using. It works fine if number (G3 cell) is exact. It works if it isn’t, but it returns the lower value. I would like it to choose the next highest number if not exact, which I understand VLOOKUP cannot do. I’ve been trying my hand at combo formulas, MATCH, INDEX, etc., but can’t make it work. Any suggestions?

=VLOOKUP(G3,’98 Title Ins Search’!A1:C490,2,FALSE)


Matthew Kuo November 8, 2015 at 1:46 pm

Hi Manette,

Have you tried just putting in both conditions into the same formula? Basically, if the exact match lookup fails, run the approximate match one. Let me know if that works for you.

=IF(ISNA(VLOOKUP(G3,’98 Title Ins Search’!A1:C490,2,FALSE)),VLOOKUP(G3,’98 Title Ins Search’!A1:C490,2,TRUE),VLOOKUP(G3,’98 Title Ins Search’!A1:C490,2,FALSE))


Monu Haswani June 4, 2016 at 11:26 pm


I want use vlookup formula to search on basis of starting a number, these number can also variable as per requirement.
A Life Insurance policy starts with 71 or 98, if this starts with 71 policy number would be 8 Digit, if starts with 98 policy number would be 5 digit.
i want to search Policy Type Like Life Insurance when i enter one of these policy numbers

Pls Help ME


Marty October 13, 2016 at 12:52 pm

Read through just about all of your articles on VLOOKUP and was sad that the one answer that I was hoping to get wasn’t there. I am dealing with a spreadsheet that has a several identical lookup values (same serial number used in column A, different data in cells b-t) and cannot figure out how to have a formula look past the first lookup value found.

Name: Birthday: Day:
Tom July 08
Mary June 15
Jack October 03
Tom February 16

How would I get VLOOKUP or INDEX MATCH to return the February value for the second Tom?


Leave a Comment

{ 2 trackbacks }

  • January 3, 2016
  • January 3, 2016

Previous post:

Next post:

Related pages

stacked bar graphs excelhow to create a frequency histogram in excelcountif on exceldefinition of vlookup in excelwrite down the 3 different formulae of ms-excelgaussian distribution in excelhow to use hlookup formula in excelexcel formulas and functionucla ticket centervariation formula excelcol_index_num vlookupucla anderson webmailvlookup for two columnsgenerating random numbers in excelucla anderson scheduleucla accounting departmentvlookup problemexcel numberingwhat is a vlookup in excelinventory format in excelsearch duplicate in excelif statement excel 2010excel compare to columnshow to sum alternate cells in excelvba excel delete columnvba formula in cellexcel formula hlookup exampleexcel vba templateswhat are vlookupsuse of vlookup and hlookup in exceleasy vlookup instructionsbar chart excel templateassignment trackerboundaries circle chartnormal probability plot excel 2010how do you put a tick in excelshortcut key in excelhow to insert a function in excelexcel find duplicates in columnhistogram on excelexcel stacked bar graphexcel macro enabledprimo pdf reviewstandard deviation chart exceladvanced vlookup excel 2010syntax of vlookupeliminate duplicates in excel 2010hide duplicates excelrand formula in excelexcel if then formula with multiple conditionshow to perform vlookup in excelexcel freeze formulaexcel show duplicate valuesfree random name drawing generatorirs income tax estimatorexcel 2010 iferrorhow to do vlookup on excel 2010why does vlookup return 0tick in excelfind duplicates in two columnshow to delete macros in excelhow to add a chart title in excelzs associates careercolumn formula excelhow to write excel formulasvlookup for dummies tutorialin excel vlookup formula exampleexcel symbol shortcutswingdings 2 tickdelete duplicates from excelhow to use hlookupexcel formulas 2007 with examplesexcel detect duplicatesexcel vlookup 2 criteriawindings 2housing construction cost variables excelhlookup formula in excel with examplepseudonym generator freelookup from pivot tablevlookup on multiple criteriaformula vlookup in excel