Excel VLOOKUP Function - Microsoft Excel VLOOKUP Function Tutorial

Excel VLOOKUP Function - Microsoft Excel VLOOKUP Function Tutorial

Excel VLOOKUP Function - Microsoft Excel VLOOKUP Function Tutorial

The VLOOKUP Function looks up a value you designate in the first column of worksheet area and returns a value from the row it finds the lookup value on. Used to merge tables, construct dashboards and perform various data processing tasks.

What is the Microsoft Excel VLOOKUP Function?

The VLOOKUP function is a computer program that you run from a worksheet cell formula. It looks up a value you designate in the first column of  worksheet area (called a range) and returns a value from the row it finds the lookup value on. In other words it is a standard table lookup that you learned in school. You run the VLOOKUP worksheet function by typing its name in a formula then followed by the information it is suppose to use. The VLOOKUP worksheet function is used in applications when you have to look up prices, name information, scientific data anything that is contained in a table. It can also be used to merge two tables together transferring information from one to the other. The V in VLOOKUP stands for vertical. There is also another function called HLOOKUP that looks along the first row of a table. Use VLOOKUP when your comparison values are located in the first column of the data that you want to find. Note that this function also has the capability to search for parts of text within other text when evaluating its lookup value.


How Do You Type the VLOOKUP Worksheet Function in a Formula?

Whenever you type a formula in a worksheet cell, this is called syntax or grammar. The general VLOOKUP function syntax has a format like this when you type it in a worksheet cell:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Where lookup_value, table_array, col_index_num, [range_lookup] are called the function argument list. Remember, you are running a computer program at this point so the program needs information to operate and that is why there is an argument list. When you see an argument list and you see square brackets [ ] around the argument name, this means the argument is optional and you do not have to include it when typing unless you need it. So for the syntax above, you need to include 3 arguments for the VLOOKUP worksheet function when typing it in a worksheet cell formula in order for it to calculate correctly. What argument values can be used are discussed below. Remember functions expect certain things in their argument lists, if you do not put the correct information in the list they will generate an error when run.


VLOOKUP Worksheet Function Argument Definitions

* It is possible to name a cell or group of cells on a worksheet and use that name in place of a range reference or cell reference. Consult Excel help on how to name a cell.


How Do You Run the VLOOKUP Function?

Since the VLOOKUP function is a computer program, it runs when you press Enter to enter the formula that contains it. If any of the arguments are wrong, the function will return an error.


What Do I Type for a VLOOKUP Function Argument?

When typing the VLOOKUP function in a worksheet cell formula, you need to replace the argument list with arguments separating each one with a comma (,). Some typical arguments you can use are:

Cell Formula Explanation
=VLOOKUP("Widget1", A1:D5, 2, FALSE) Find Widget1 in Column A then return information from Column B the row that Widget1 was found on
=VLOOKUP("*ST*", G1:J5, 4, FALSE) Find the piece of text ST inside the cell value text in Column G then return information from Column J the row that ST was found on
=VLOOKUP("*" & B1 & "*", G1:J5, 2, FALSE) Find the piece of text from the B1 cell value inside the text in Column G then return information from Column H the row that it was found on
=VLOOKUP(16, B1:D5, 2, FALSE) Find number 16 in Column B then return information from Column C the row that 16 was found on
=VLOOKUP(B1, Prices, 3, FALSE) Find the B1 cell value in range name Prices first column then return information from 3rd Column in range the row that B1 cell value was found on *

* This function is used a great deal in building adaptive formulas that figure out ranges, start and stop of data.... It is also heavily used in dashboard design tying numeric ActiveX control outputs into formulas that seek out data in tables.


Additional VLOOKUP Function Examples

The worksheet seen below contains some typical worksheet formulas that run the VLOOKUP worksheet function. Pay close attention to the argument list and the syntax used to write the formula.

A B C
1 Searching by Names
2
3 Item Cost
4 Widget1 $1.25
5 Widget2 $2.50
6 Widget3 $3.50
7
8 Look up name Widget1 in table and return cost. Formula below
9 Cost $1.25 = VLOOKUP( "Widget1", A3:B6, 2, FALSE )
10
11 Item Widget1
12 Cost $1.25 = VLOOKUP( B11, A3:B6, 2, FALSE )
13
14 Item Widget1 Allow user to search, use range name Widgets in place of A3:B6 much easier, can be on different sheet. Formula below
15 Cost $1.25 = VLOOKUP( B14, Widgets, 2, FALSE )
16
17
18 Searching by Characters
19
20 SKU# Cost
21 X-ST1-100 $1.25
22 V-ST2-200 $2.50
23 Z-ST3-300 $3.50
24
25 Searches for X-ST1 in SKU# column, lookup value based on first 5 characters using an * after the characters, return cost. Formula below
26 Cost $1.25 = VLOOKUP( "X-ST1*", A20:B23, 2, FALSE )
27
28 Searches for T3 in SKU# column, lookup value based on T3, using * before and after the characters, return cost. Formula below
29 Cost $3.50 = VLOOKUP( "*T3*", A20:B23, 2, FALSE )
30
31 Search T3 Searches for T3 in SKU# column, lookup value based on cell value B31 which is T3, using * before and after B31 and & to combine with *, return cost. Formula below
32 Cost $3.50 = VLOOKUP( "*" & B31 & "*", A20:B23, 2, FALSE )
33
34
35 Searching by Numbers
36 InTake Temp (deg F) Correction Factor
37 95 .986
38 100 .974
39 105 .962
40 110 .95
41 115 .937
42 120 .925
43 125 .913
44 130 .9
45 Using an approximate match, 4th argument omitted, searches for the value 99 in Column A and finds the closest match 95, returns .986. Formula below
46 Corr Factor .986 = VLOOKUP( 99, A36:B44, 2 )
47 Using an exact match, 4th argument is FALSE, searches for the value 90 in Column A and returns error not found. Formula below
48 Corr Factor #N/A = VLOOKUP( 90, A36:B44, 2, False )
49

A B C D E
1 Merging Tables
2 Item Cost SKU# Item SKU#
3 Widget1 $1.25 = VLOOKUP( A3, $D$2:$E$5, 2, False ) Widget1 X-ST1-100
4 Widget2 $2.50 Widget2 V-ST2-200
5 Widget3 $3.50 Widget3 Z-ST3-300
6 Using an absolute match, use values in column A as lookup in column D to return SKU# to column C. Formula above

Things to Know About the VLOOKUP Function

Next Excel Training Dates - Public Training and Live Online Courses

Our next public Excel training workshops and live online Excel courses are listed below. For all of the dates and times, please visit our Training Schedule page using the link above in the main menu.

Business, Engineering and Science
Beginning Excel Training for Business Professionals Intermediate Excel Training for Business Professionals Advanced Excel Training for Business Professionals Beginning Excel VBA Training for Business Professionals Intermediate Excel VBA Training for Business Professionals Advanced Excel VBA Training for Business Professionals Advanced Excel Training for Engineers / Scientists Beginning Excel VBA Training for Engineers / Scientists Intermediate Excel VBA Training for Engineers / Scientists Advanced Excel VBA Training for Engineers / Scientists Microsoft Excel Dashboards Training for Combined Audience
Beginning Excel Training for Business
Next Date:
See Schedule
Intermediate Excel Training for Business
Next Date:
See Schedule
Advanced Excel Training for Business
Next Date:
See Schedule
Beginning Microsoft Excel VBA Training for Business
Next Date:
See Schedule
Intermediate Microsoft Excel VBA Training for Business
Next Date:
See Schedule
Advanced Microsoft Excel VBA Training for Business
Next Date:
See Schedule
Advanced Excel Training for Engineers / Scientists
Next Date:
See Schedule
Beginning Excel VBA Training for Engineers / Scientists
Next Date:
See Schedule
Intermediate Excel VBA Training for Engineers / Scientists
Next Date:
See Schedule
Advanced Excel VBA Training for Engineers / Scientists
Next Date:
See Schedule
Microsoft Excel Dashboard Training
Next Date:
See Schedule

Public Training Location

  • Santa Barbara, CA
  • Los Angeles, CA
  • Pasadena, CA
  • Reseda, CA
  • Chatsworth, CA
  • Topanga, CA
  • Santa Clarita, CA
  • Newbury Park, CA
  • Camarillo, CA
  • Calabasas, CA
  • Valencia, CA
  • West Hollywood, CA
  • Goleta, CA
  • Palmdale, CA
  • San Fernando, CA
  • Burbank, CA
  • La Canada, CA
  • Altadena, CA
  • Culver City, CA
  • Rosemead, CA
  • Anaheim, CA
  • El Segundo, CA
  • Fillmore, CA
  • Gardena, CA
  • Encino, CA
  • Oxnard, CA
  • Mojave, CA
  • La Crescenta, CA
  • Glendale, CA
  • Flintridge, CA
  • Beverly Hills
  • Arcadia, CA
  • Inglewood, CA
  • Hawthorne, CA
  • Manhattan Beach, CA
  • Santa Paula, CA
  • Century City, CA
  • Sherman Oaks, CA
  • Reseda, CA
  • Woodland Hills, CA
  • Hollywood, CA
  • Studio City, CA
  • Fillmore, CA
  • Ventura, CA
  • Moorpark, CA
  • Simi Valley, CA
  • Santa Monica, CA
  • Encino, CA

EMAGENIT performs Excel onsite company training in the cities listed above as well as the entire Northern and Southern California areas, United States and Canada.

LinkedIn
Facebook
Information
Need Help? Please call us at 1.866.924.6244

Copyright © 2002-2017

EMAGENIT All Rights Reserved