Skip to main content

Excel VLOOKUP Function

Excel VLOOKUP Function

Summary 
VLOOKUP is an Excel function to lookup and retrieve data from a specific column in table. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. The "V" stands for "vertical". Lookup values must appear in the first column of the table, with lookup columns to the right.
Purpose 
Lookup a value in a table by matching on the first column
Return value 
The matched value from a table.
Syntax 
=VLOOKUP (value, table, col_index, [range_lookup])
Arguments 
  • value - The value to look for in the first column of a table.
  • table - The table from which to retrieve a value.
  • col_index - The column in the table from which to retrieve a value.
  • range_lookup - [optional] TRUE = approximate match (default). FALSE = exact match.
Usage notes 
VLOOKUP is designed to retrieve data in a table organized into vertical rows, where each row represents a new record. The "V" in VLOOKUP stands for vertical:
VLOOKUP is for vertical data
If you have data organized horizontally, use the HLOOKUP function.

VLOOKUP only looks right

VLOOKUP requires a lookup table with lookup values in the left-most column. The data you want to retrieve (result values) can appear in any column to the right:
VLOOKUP can only look to the right

VLOOKUP retrieves data based on column number

When you use VLOOKUP, imagine that every column in the table is numbered, starting from the left. To get a value from a particular column, simply supply the appropriate number as the "column index":
VLOOKUP exact match example
=VLOOKUP(H3,B4:E13,2,FALSE) // first
=VLOOKUP(H3,B4:E13,3,FALSE) // last
=VLOOKUP(H3,B4:E13,4,FALSE) // email

VLOOKUP has two matching modes, exact and approximate

VLOOKUP has two modes of matching: exact and approximate, which are controlled by the 4th argument, called "range_lookup". Set range_lookup to FALSE to force exact matching, and TRUE for approximate matching.
Important: range_lookup defaults to TRUE, so VLOOKUP will use approximate matching by default:
=VLOOKUP(value, table, column) // default, approximate match
=VLOOKUP(value, table, column, TRUE) // approximate match
=VLOOKUP(value, table, column, FALSE) // exact match

Example 1: Exact match

In most cases, you'll probably want to use VLOOKUP in exact match mode. This makes sense when you have a unique key to use as a lookup value, for example, the movie title in this data:

The formula in H6 to lookup year based on an exact match of movie title is:
=VLOOKUP(H4,B5:E9,2,FALSE) // FALSE = exact match

Example 2: Approximate match

You'll want to use approximate mode in cases when you're looking for the best match, not an exact match. A classic example is finding the right commission rate based on a monthly sales number. In this case, you want VLOOKUP to get you the best match for a given lookup value. In the example below, the formula in D5 performs an approximate match to retrieve the correct commission.
VLOOKUP approximate match commission rate
=VLOOKUP(C5,$G$5:$H$10,2,TRUE) // TRUE = approximate match
Note: your data must be sorted in ascending order by lookup value when you use approximate match mode with VLOOKUP.
More about VLOOKUP
  • Detailed VLOOKUP examples
  • 23 things you should know about VLOOKUP

Other notes

  • Range_lookup controls whether value needs to match exactly or not. The default is TRUE = allow non-exact match.
  • Set range_lookup to FALSE to require an exact match and TRUE to allow a non-exact match.
  • If range_lookup is TRUE (the default setting), a non-exact match will cause the VLOOKUP function to match the nearest value in the table that is still less than value.
  • When range_lookup is omitted, the VLOOKUP function will allow a non-exact match, but it will use an exact match if one exists.
  • If range_lookup is TRUE (the default setting) make sure that lookup values in the first row of the table are sorted in ascending order. Otherwise, VLOOKUP may return an incorrect or unexpected value.
  • If range_lookup is FALSE (require exact match), values in the first column of table do not need to be sorted.

Comments

Popular posts from this blog

Know about Excel Tables

23 things to know about Excel Tables by Dave Bruns | May 17, 2018 Excel Tables have a boring (and confusingly generic) name, but they are packed with useful features. This article is a summary of the things you should know about Excel Tables. 1. Creating a table is fast You can create an Excel Table in less than 10 seconds. First, remove blank rows and make sure all columns have a unique name, then put the cursor anywhere in the data and use the keyboard shortcut Control + T. When you click OK, Excel will create the table. 2. Navigate directly to tables Like named ranges, tables will appear in the namebox dropdown menu. Just click the menu, and select the table. Excel will navigate to the table, even if it's on a different tab in a workbook. 3. Tables provide special shortcuts When you convert regular data to an Excel Table, almost every shortcut you know works better. For example, you can select rows with shift + space, and columns with control

Microsoft Excel Keyboard Keys

You could learn here about MS excel basis and some advance sort keys. Formatting sort keys. CTRL+1                    Format Box ALT+E+S+T             Copy Format ALT+H+0                 Increase Decimal ALT+H+9                 Decrees Decimal  CTRL+SHIFT+7     Boxing ALT+O+C+A            Fit Column Width ALT+H+O+R            Change Sheet Tab Name ALT+W+F                (Un)Split Panes ALT+W+S                 (Un)Freeze Panes SHIFT+CTRL+#       Date format SHIFT+CTRL+S      $ Dollar format SHIT+CTRL+%       % Percentage format ALT+"="                   Sum function With CTRL Shortcuts. CTRL+A       Select All CTRL+B         Bold (only selected cell or range) CTRL+C         Copy(only selected cell or range) CTRL+D         Fill down(Select non blank cell or range) CTRL+E          Flash Fill CTRL+F          Find CTRL+G         Go To Special CTRL+H         Replace CTRL+I           Italic CTRL+K          Insert Hyperlink CTRL+L          Create Table C

Why Learn Excel?

A nalysts, consultants, marketing professionals, bankers, and accountants all use Excel on a consistent basis. You might even find that other random professionals like graphic designers and engineers are working away with the powerful formulas and charts that come with Excel.   Seeing as how Excel is so prominent in the workforce, it pays to learn all about it and put that experience on your resume. You might find that your current degree isn’t yielding any job prospects, so getting into Excel could get you a job in the meantime. Developing your Excel skills using online resources could improve your employment prospects without the need to spend a lot of money on college courses. Learning Excel might even improve your job opportunities if you lack educational experience. People who didn’t go to college can get into coding, of course, but the same can be said about Microsoft Excel. Upon completing the right training, you automatically make yourself more valuabl