Table of Contents

Vlookup - Documentation

Created: August 25, 2024
By: Raza Tahir
Thank you for reading!
Back to Learn

Reminder: Please download the files that were given.

For additional reading, I would recommend reading the reference articles from Microsoft and Google .

Getting Started

In Excel & Google Sheets, VLOOKUP is a premade lookup function that can find information in a row.
I.E. Given any row, I can find information from other columns in that row.

Each record/entry must be in a separate row for VLOOKUP to work.
E.g. Given someones ID Number, find their domain handle.

# First Last Handle
1 Mark Otto @mdo
2 Jacob Striver @Striver
3 Larry Bird @twitter
VLOOKUP can also be used:
  • in Product/Inventory lists where VLOOKUP helps find an items price or details by using the part number.
  • for finding employee information and specific details (e.g., Name, Email, Department) using their unique EIN.

Now, let’s go over the VLOOKUP Function Syntax.

This function takes in four arguments:
    lookup value, table array, column index number, and range lookup.
  • Lookup Value: The value you want to search for. Remember that this identifier must be in the first column.
  • Table Array: The dataset(range) you’re working with and will use for the search.
  • Column Index Number: Based/Relative on the table array, this number represents the column from which you want to retrieve the value.
  • Range Lookup: Indicates whether you're looking for an exact(FALSE) or approximate(TRUE) match.

The following examples will help clarify this function. The key is not to overcomplicate things since this function simply returns a value from another column.

Use the FX tool/Insert Function dialog box to simplify and break down the function for easier understanding.


B) Example #1:

Let's look at the "Example 1" Worksheet.

You can open the file by:
  1. Going to the file directory.
  2. Open the folder named "Vlookup Examples"
  3. Open up "Example 1".

Suppose below we have a table of some produce:

In our dataset i.e. "Table Array", each produce has a price per pound and availability.
Produce Available? Price
Apple Yes $1.50
Banana No $2.03
Lemon No $3.10
Orange Yes $1.01

In the above example, we can find the price of a fruit (e.g. Orange) simply by looking at the data table. But for a store with hundreds of products with other relevant columns, it would be very daunting to look row by row to find our product! So instead we can use a "Lookup Table" to find the price easily.

Below is an example:

A Lookup Table can allow us to search for a fruit.
Price? =VLOOKUP(B1, Dataset, 2, FALSE);
Available? =VLOOKUP(B1, Dataset, 3, FALSE);
Note: A VLOOKUP simply looks up related values in a row. In this example,
  • Cell B1 has the fruit item we’re looking up (i.e. our lookup value)
  • Cells B2 and B3 contain the VLOOKUP function that displays the price and stock based on B1 and updates automatically as we change B1.

In short, if we know a fruit, we can use VLOOKUP to find both its price and stock status.
E.G, To find the price of Oranges, type 'Orange' in cell B1 (the lookup value) to find its price and availability (B2 & B3).

Now, let’s break down how VLOOKUP works:

  • Lookup Value (B1): This is what we're searching for, like cucumbers. NOTE: Lookup values are not case sensitive
  • Table Array (A7 to C11): This range includes all our data. The header row is usually excluded but can be added.
  • Column Index Number: This identifies which column to pull data from— 1 for vegetables, 2 for price, and 3 for stock. For cell B2 (price), we use the number 2 and for cell B3 (stock) we'll use 3.
  • Match: We will use EXACT match since we're looking up an exact fruit that's spelled correctly.
    Data validation can be applied on B1 to ensure no spelling mistakes. That way, the user doesn't mispell the name of the fruit.

Question: If we move the data set to another location, will the function still work?

Yes because the function uses cell references, the column index for the price remains to be 2 regardless of whether the data set moves or not.


C) Example #2:

Let's now open up the Example #2 Spreadsheet.

Let's look at another example using a payroll sheet with names, roles, join dates, and total income.
Seen below is an example of the payroll data set and the lookup table:

Data Source Table
Name Position? Salary
Raza Trainer $1M
Zehra English Teacher $2M
Sarah Director of Training $4M
Lookup Table
Position (VLOOKUP goes here)
Salary (VLOOKUP goes here)

In the lookup table, when we enter a person's name in cell F1, cells F2 and F3, which contain the VLOOKUPs, will retrieve their role and salary.

Question: In the data source table, imagine if there is a wrong name recorded like "Raza Tahir" or "Razaaa" in cell A2.
If I now type "Raza" correctly in the lookup table in cell F1, will the functions still work?

No. Although now we are changing the record entry value, there are no people whose name is exactly "Raza" to find so the NA error will be given.

The answers below will show what each VLOOKUP should look like:

= VLOOKUP (F1, A2:C4, 2, FALSE)

= VLOOKUP (F1, A2:C4, 3, FALSE)


The only difference between the 2 vlookups functions is the output; one outputs price and the other output salary.
Therefore, the difference between each functions syntax is the parameter column index number.

Please note that the lookup value for both functions is the same, cell F1. The value of F1 depends on the user so if the user selects "Sarah" for F1, the VLOOKUPs in F2 and F3 will show Sarah's position and department.

So the question that must be asked is this:

Question: If I have two cells with the same name (e.g. "Raza" in both A2 and F1), does it make a difference if the lookup value in the VLOOKUP is F1, A2, or even the text "Raza"?

Question: In the data source table, if I added another name, how will our functions change?

By adding an extra name, our data set becomes bigger. So our table array is what changes.

In the VLOOKUP function, for the Table Array parameter if we use a table name, then our function will stay the same since the table size will be the only thing that changes.
Optional Task: Add an "Email" column to our data table and assign fictional emails to everyone. Then, add a vlookup on the lookup table to find a persons email.


D) Example 3: Understanding approximate vs. exact match

Let's take a look an example where a company sells laptops to universities:

Data Source Table
University Laptops Bought Free Laptops Received
CSUN 70 -
UCLA 103 5
UCB 151 5
UCI 199 5
Free Incentive Lookup Table
Laptops Order Volume Free Giveaways
0 0
100 5
200 10
300 15

The lookup table shows incentives for purchasing laptops in multiples of 100. For example:

  • Buying 100 laptops earns 5 free laptops
  • Buying 200 laptops earns 10 free laptops,
etc.

*For this case, the VLOOKUPs are in column C of the data source table. E.G. In C3, the function is:
=VLOOKUP(B3,LOOKUP-TABLE,2,TRUE);

Since universities have their own individual needs, universities are allowed to purchase any arbitrary amount of laptops (including non-multiples of 100). So for example:

  • If a client orders 103 laptops, they receive 5 free laptops.
  • If a client orders 151 or even 199 laptops, they'll only get 5 free laptops because VLOOKUP rounds down to the nearest multiple. If we need to be generous and round up, we use the ROUND() function.
    XLOOKUP simplifies this by offering an extra parameter that can be used to round up if needed, thereby eliminating the need for an additional function.

In other words, an approximate match can round/estimate our lookup values since those don't appear on the lookup table. In other cases, we use exact match when searching for a unique identifier (E.g., Product Number, Student ID, etc)

For cell C3, let's take a dive into the syntax our VLOOKUP:

  • The lookup value is the number of laptops purchased (Cell B3 in this case since it's UCLAs purchase quantity)
  • The table array contains the incentive ranges. This time we're looking at the lookup table!
  • The column index number is 2 to view the free laptops giveaways column.
  • Range lookup is set to TRUE to allow approximate matching.


E) Xlookup

XLOOKUP is the new and improved version of VLOOKUP function that improves on the limitations of VLOOKUP. The benefit of using VLOOKUPs is that they are more widely supported & compatible with older Excel versions.

Note: On Microsofts website , they say:

"XLOOKUP is not available in Excel 2016 and Excel 2019, however, you may come across a situation of using a workbook in Excel 2016 or Excel 2019 with the XLOOKUP function in it created by someone else using a newer version of Excel."

Some reasons as to why you should use XLOOKUP over VLOOKUP:

  • Works faster for larger data sets.
  • Substitutes for the VLOOKUP and HLOOKUP functions.
  • Since XLOOKUP uses arrays and not data sets, columns become irrelavant.
    • E.G. We can add empty columns. Also, there's no need to count columns when retrieving information.
    • The lookup value isn't required to be in the first column since the lookup range can be any row/column
  • When looking up multiple values, use 1 XLOOKUP to spill values instead of writing multiple VLOOKUPs.
  • Interacts with APPROXIMATE matches more accurately
  • Provides in-built error handling that omits the need for IFERROR function

Let's take a look at an example:

Data Source Table
Country Abbreviation Prefix
China CN +86
Brazil BR +55
United States US +1
Russia RU +7
Mexico MX +52
Dial Code Lookup Table
Dial Code?
=XLOOKUP(E2, A2:A6, C2:C6)

In this example, XLOOKUP works just like VLOOKUP and provides the same answer.

XLOOKUP has a total of 6 parameters. Only the first 3 are required and the last 3 are optional. Here are 3 main ones to know:

  • Lookup Value: The value you want to search for, similar to VLOOKUPs 'lookup value'.
  • Lookup Array: This is the range in which you search for the Lookup Value. I.E. The lookup value should be in this range.
    This range does not have to be the first column!
    This range can be any single column or row, thereby making up for the HLOOKUP function as well!
  • Return Array: This is the range where the result should be found. XLOOKUP maps the Lookup & Return arrays.
    The ranges size should be the same as the lookup array.

F) Sources and Credits

Reference Links

Like stated above, to learn more use the following reference links to these reference articles:


Back to www.its.raza.com

Back to Top