The SDS Blog: How To Use Excel’s Most Popular Function - The VLOOKUP (step-by-step) - SuperDataScience - Big Data | Analytics Careers | Mentors | Success

The SDS Blog: How To Use Excel’s Most Popular Function – The VLOOKUP (step-by-step)

(Time to Read: 4 mins)

The VLOOKUP function enables you to look up data corresponding with the row of a lookup value and corresponding column index.

For example, the VLOOKUP function can use the relational data elements of two separate tables and append the data from one table to the other table.

Alternatively, you can create a simple Excel form that uses a VLOOKUP to lookup and present data from one or more tables.

Don’t worry if this sounds a tad complicated, by the end of this article, you’ll have created your own (and perhaps first?) VLOOKUP.

Understanding its core

Before I show you VLOOKUP in action, you need to know what it consists of.

In Excel, this is called the “Syntax”. In VLOOKUPs case, the syntax consists of 4 different arguments

Syntax:

=VLOOKUP (lookup_value,table_array, col_index_num,[range_lookup])

Arguments:

  1. lookup_value – The value to look for in the first column of a table.
  2. table_array – The table specified as a range from which to retrieve a value.
  3. col_index_num – The column in the table from which to retrieve a value.
  4. range_lookup – [optional] TRUE = approximate match (default). FALSE = exact match.

To make it super easy for you, I’ve included a sample data file. Download that now and open it up as we’re going to use from here on out.

Okay, so we’ll start by analyzing the data that exists in sample sales data.

This data has been organized and separated into two different worksheets, each worksheet containing a different table: Order and People.

Appending data to the Orders

The “Orders” table contains sales transactions but does not include the salesperson.

We have the salesperson and region data in the “People” table. Using VLOOKUP, we can append the data we have for the salesperson to the Orders table by using the Region data as the lookup value.

Insert a column in the Order table and label it “Salesperson”.

Insert the following syntax into cell C2:

=VLOOKUP(B2,People!$A$2:$B$5,2,FALSE)

Let’s break it down:

Argument Value Description
lookup_value B2 Represents the lookup value, and in this case, it is the Region
table_array People!$A$2:$B$5 Second table which contains Salesperson and Region data
col_index_num 2 For the specific region, the value of “2” instructs Excel to use the value in column 2
range_lookup FALSE Using FALSE instructs Excel to find an exact match

Note that the table_array argument People!$A$2:$B$5 is anchored, treating the range as an absolute range.

As you copy the formula down the table, the reference to A2:B5 of the People worksheet remains constant.

You’ve just taken data from the People table and appended it to the Orders table. Good job!

A more advanced use of the VLOOKUP is to use it in conjunction with a Combo Box form control – and use that to look up orders. VLOOKUP can be used to look up the details of each order.

Let’s see how to do that!

Access “Form Control” using the Developer ribbon and insert a Combo Box from the “Form Controls” menu.

Once the from control is inserted, open the properties of the Control

  • The Input range should reference the Order ID column of the Order table.
  • The Cell link, in this case B2, is the cell which will display the index value corresponding to the Order ID.
  • Optionally, you can change the number of lines to display in the control. In this case we will specify 10 rows.

Once the form control has been added and configured, place the fields from the Orders table to create your own form.

Once that is complete, utilize the VLOOKUP function and reference cell B2 as the lookup value. Place as many fields as you would like in your form using the same

=VLOOKUP($B$2,Orders!$A$2:$V$9995,3,FALSE)

Once again, let’s “x-ray” the function:

Argument Value Description
lookup_value B2 Represents the lookup value, in this case Row ID
table_array Orders!$A$2:$V$9995 Refers to the table which contains Order data
col_index_num 3 For the specific Row ID, the value of “3” instructs Excel to use the value in column 3 corresponding to the Row ID.
range_lookup FALSE Using FALSE instructs Excel to find an exact match

For each field added to the Excel form, use the corresponding column number.

And there you have it: A tool to easily look up orders, combining VLOOKUP and a Combo Box!

There’s much more to VLOOKUP (which you can read about here) but congratulate yourself on just having learned Excel’s most popular function (literally!).

Got questions or comments?

Hit me!

0

Sebastian Moncada
Sebastian Moncada

Content Manager

What are you waiting for?

EMPOWER YOUR CAREER WITH SUPERDATASCIENCE

CLAIM YOUR TRIAL MEMBERSHIP NOW
as seen on: