Background Information
Excel provides a powerful way to locate information in a spreadsheet using the VLOOKUP function. The VLOOKUP function searches the left column of a range of cells (lookup_table) for a specified value (lookup_value). Once that value is found, Excel returns a value from a different column (col_num).
Function Syntax
When you create a VLOOKUP function in Excel, it must follow a very specific format, or syntax. In order for Excel to recognize a VLOOKUP function, it must be written in the following manner:
=VLOOKUP(lookup_value, lookup_table, col_num, range_lookup)
|
lookup_value
|
lookup_table
|
col_num
|
range_lookup
|
|
What am I looking up?
|
Where am I looking for it?
|
In which column number is the information located?
|
Do you want an exact match?
|
|
The value to be found in the first column of the table that is being searched.
|
The table of information in which the lookup_value is being searched.
|
The column number in the lookup_table from which the matching value must be returned.
|
Optional – Allows you to specify if you are looking for an exact match or an approximate value.
TRUE = approximate match
FALSE = exact match
|
V-Lookup Example
The spreadsheet in the attached pdf demonstrates how to use a VLOOKUP function. The table to the right is considered a “lookup table”. In this example, when users type a badge number into cell J3 the corresponding name and tips will appear in cells K3 and L3.
- Cell J3: This is the cell where you type the badge number you are looking for.
- Cell K3: This function will look for the badge number you typed (J3) in the range of cells A3 to H20… Once it finds a match (row 14), it will get the value from the eighth column of the range (8) and put the value into cell L3... Adding FALSE at the end means Excel will only find exact matches.
- =VLOOKUP(J3,A3:H20,2,FALSE)
- Cell L3: This function will look for the badge number you typed (J3) in the range of cells A3 to H20… Once it finds a match (row 14), it will get the value from the seventh column of the range (7) and put the value into cell L3... Adding FALSE at the end means Excel will only find exact matches.
- =VLOOKUP(J3,A3:H20,6,FALSE)
Sample Exercise
Please review the example within the pdf attachment and complete the VLOOKUP function based on the worksheet shown.
=VLOOKUP(lookup_value, lookup_table, col_num, range_lookup)
|
lookup_value: ___________
lookup_table: ___________
col_num: ___________
range_lookup: ___________
|
Badge:
__________________________
Proper Name:
__________________________
2008 Pay:
__________________________
|
|
lookup_value: ___________
lookup_table: ___________
col_num: ___________
range_lookup: ___________
|
Badge:
__________________________
Proper Name:
__________________________
2008 Pay:
__________________________
|
To review images or save/download/print a copy of this Job Aide, please select the pdf attachment.