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.

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.

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.

- lookup_value: The value to search for in the first column of your data. Lookup_value can be a value, a cell reference or formula. Be sure there are no repeated values in this column. If there are, VLOOKUP will lock onto the first one encountered and ignore the rest. These values searched for in the first column can be text, numbers or logical values. If text is present, they are not case sensitive. If searching for a text value, you can use wild card characters like ? and * to go after a specific character or a group of characters. You can also use the ampersand (&) to help concatenate the lookup value on the fly. Be sure to surround text lookup values with quotes " ", numeric lookup values do not take quotes.
- table_array: Two or more columns of data, in other words a range (we call it a table outside of Excel). You can identify this range by range reference (i.e. A1:B4) or use a range name like sales*. You can include the headers of the data in the reference as long as they do not interfere with the lookup_value search. * 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.
- col_index_num: The column number in your range that contains the value you want to return. For example, you have two columns of data located in columns C and D. The lookup column is C, it contains the values to scan and column D contains the values to return. C is considered column 1 and D is considered column 2. If the VLOOKUP finds the value in column C at row 4 and you tell it to return column 2, then it would return the value of D4. Remember the column number system that you use is relative to the range of data, not worksheet column location. So if you data starts in column G that is column 1 to VLOOKUP. If you do not indicate the proper column say, you put 4 instead of 2 and there are only 2 columns of data, an error will be returned.
- range_lookup
(Optional): VLOOKUP can find an absolute match or
approximate match in the first column of data it is looking
in. Use TRUE for this argument if you want an approximate
match or FALSE if you want an absolute match.
- If you use TRUE or do not include it, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned. The values in the first column of your data must be placed in ascending sort order; otherwise, VLOOKUP may not give the correct value. You can put the values in ascending order by sorting the data.
- If you use FALSE, VLOOKUP will only find an exact match. In this case, the values in the first column of data do not need to be sorted. If there are two or more values in the first column that match the lookup_value argument, the first value found is used. If an exact match is not found, the error value #N/A is returned.

* 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.

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.

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.

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 |

- When searching text values in the first column of your data, ensure that the data in the first column does not have leading spaces, trailing spaces, inconsistent use of straight ( ' or " ) and curly ( ‘ or “) quotation marks, or nonprinting characters. In these cases, VLOOKUP may give an incorrect or unexpected value. For more information on functions that you can use to clean text data, see the Text functions.
- When searching number or date values, ensure that the data in the first column of data is not stored as text values. In this case, VLOOKUP may give an incorrect or unexpected value. For more information, see Convert numbers stored as text to numbers in help.
- If the range_lookup argument is FALSE and the lookup_value argument is text, then you can use the wildcard characters, question mark (?) and asterisk (*), in lookup_value. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.

Need Help? Please call us at 1.805.498.7162

Excel Training Services

Microsoft Excel Products

EMAGENIT Company Information

Copyright © 2002-2019

EMAGENIT All Rights Reserved