VLOOKUP

The vlookup function is useful when you have two tables, where both tables have a common identifier such as a primary key from a database table, and you want to add column to table 1 with data pulled from a matching row in table 2.

Example:

=IFERROR(VLOOKUP(A3,Sheet1!A:B,2,FALSE),"")

• the VLOOKUP function is “wrapped” in an IFERROR function above so that any results where matches are not found will show blank instead of the Excel default of “#N/A”
• A3 holds the common identifier (primary key) in table 1
• Sheet1!A:B defines the table 2 area
• 2 is saying we want to fetch and display the value of the 2nd column of table 2
• FALSE says we did not sort the common identifier (primary key) in table 2

• table 2 must have the common identifier as the first column, although you don't have to select an entire sheet - you can define cells or columns within a sheet
• if the identifier in table 2 isn't sorted, the last parameter of the vlookup function must be FALSE
• if you are having trouble getting results and your common identifier is a number, make sure that values in both tables are actually numbers
  • look for the green arrow in the upper left of a cell, and a yellow flag to the right; clicking on the yellow flag allows you to convert one or more cells to a number
  • Convert Text to Number
• if you are comparing text, make sure you don't have odd characters in the strings, or empty space at the beginnings or ends; press F2 and examine a definite match carefully to find problems

• Solutions to Three Common Problems when Using VLOOKUP()