What is a VLOOKUP?

• A VLOOKUP is used for comparing two or more lists in excel.
• It shows you what names the two lists have in common by comparing a column as a “vertical look up” with another list/spreadsheet to bring in the value

Frontier example:

We have our revenue report with a list of companies who we know are clients (because we have revenue from them) – let’s refer to this as list B. Now we also have an excel file containing contacts from another list (let’s call it list A) and we need to figure out how accurate this list A is.

• If you need to figure out if list A is missing names, and you know that list B is accurate, you can use list B to verify what names are missing from list A

The Formula:

=VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE).

Example:

In the example below, we’re trying to figure out how much each person on list A donated or when the graduated, based off of the information from list B.  

Formula explained in Detail:

• The first portion of the formula “F2” in List A, represents the thing you’re trying to find – How much did Kevin donate or when did he graduate. (note if you’re trying to compare the entire list A, highlight the entire column F)
• The second portion of the formula “A:C” represents the section of data, from list B that you’re using to find your answer (highlighted in pink)
• The third portion of the formula “2” represents up to which column from list B you want to find the answer for.
  • If you want to find the dollar amount of how much each person donated, select the entire column’s A&B and type in 2, and it will return the values that are from the second column.
  • If you want to see what year each person in the list graduated, select A,B & C then type in 3 and it will return the graduation year, the third column.
• The forth portion of the formula “FALSE or TRUE”
  • When you use FALSE, excel will return EXACT values –so this will only work if the names on the list match each other exactly.
  • When you use TRUE, excel will return APPROXIMATE values so if the names on the list match each other enough.
  • Usually use FALSE because often the names on the lists that you’re trying to compare will have different names – and it ends up returning a lot of errors.