The Ultimate Guide to VLOOKUP - Plus VLOOKUPs Without Formulas

Love playing around with spreadsheets, but haven’t tried VLOOKUP before?

Brace yourselves! You’re in for a ride.

If you spend a huge chunk of your time and effort manually retrieving and transferring data from spreadsheets, VLOOKUP is about to make your life a lot easier.

So, what exactly is VLOOKUP? And how to use it?

Let's have a look.

To help you better understand the concept of VLOOKUP, we’ve shared several examples throughout this post and have taken a practical approach. Hope you enjoy the read!
‍

Need a Guide to VLOOKUP? You’ll Probably Be Able to Relate to John!

But before we dive into the definition, let us show you what life of a spreadsheet user looks like without VLOOKUP.

John’s a performance marketer at a large eCommerce store with more than 50,000 customers and millions of dollars in revenue. He wants to build a personalized email marketing campaign to reach out to customers who purchased from their online store in Q4 2022.

He has access to a large spreadsheet with over 20,000 customer emails and their total transactional value – these customers purchased from the store in Q4 2022. Let’s name this spreadsheet “Q4 2022 Transactional Data.”

John has everything he needs to run an email marketing campaign.

However, there’s just one crucial piece of information missing, i.e., customer names.

Without their names, he would never be able to run a personalized campaign.

So, he decides to dig into the eCommerce store’s customer database to find customers' names linked to these emails.

It’s a lot of manual work – looking up customer names one by one and adding them to the Q4 2022 Transactional Data spreadsheet.

John needs to find names of customers linked to over 20,000 emails, which is a lot of manual work and will require him to spend days (unless he’s Superman).

Imagine the number of hours he would have saved if there were a way to automate the process!

Well, there is. This solution goes by the name “VLOOKUP.”

What’s VLOOKUP?

Let’s find out.

A Step By Step Guide to VLOOKUP

VLOOKUP, also called Vertical Lookup, is a function in several spreadsheet programs like Microsoft Excel, Google Sheets, and Gigasheet that allows users to retrieve specific data from one cell, table, or spreadsheet and transfer it to another.

In the above example, John can save hours by using VLOOKUP to retrieve customer names from the eCommerce store’s database and transfer it to his Q4 2022 transactional data spreadsheet.  

All he needs to do is link the customer email column group in his Q4 2022 transnational data spreadsheet with the customer email column group in the database – after which, the VLOOKUP function will automatically retrieve the names of customers and automatically transfer them to the Q4 2022 spreadsheet.

The basic syntax for VLOOKUP function in Microsoft Excel is –

Let’s simplify it a bit -

1.   lookup_value = what you want to look up

2.   table_array = where you want to look for it.

3.   col_index_num = the column number in the range comprising the value to return.

4.   [range_lookup] = whether you want it as an approximate or exact match. If you want an exact match, the value should be 0/FALSE. In case you want approximate match, the value should be 1/TRUE.
‍

What is Exact Match in VLOOKUP?

Exact Match in VLOOKUP only returns result when it finds exact match for the lookup value (lookup_value) in the specified table array (table_array).

Let’s consider a scenario where you used the Exact Match option in VLOOKUP.

If you have a list of customer names along with their respective phone numbers and if you use the VLOOKUP function to retrieve the phone number of a customer named “Will Andrews” from the “Customer Names” column, then the function will only return a result if there’s a customer under “Customer Names” column named exactly as “Will Andrews.”

Example –

Let’s not add Andrews to our formula and see if it fetches us Will’s phone number.

As expected, the formula didn’t fetch us Will’s phone number.

What is Approximate Match in VLOOKUP?

Approximate Match in VLOOKUP returns the closest match to the lookup value (lookup_value) specified.

In Microsoft Excel or Google Sheets, every time you want to retrieve data from one cell, table, or spreadsheet and transfer it to another, you need to apply a formula.

A few benefits of VLOOKUP are:

●     Makes the process of searching + retrieving data easier.

●     No restrictions when it comes to data type. You can easily retrieve and transfer any type of data, including dates, text, and numbers.

●     Helps save time.

●     Eliminates the chance of human error.

VLOOKUP is a brilliant function, but if you hate formulas (like me), you’ll find it extremely hard to search, retrieve and transfer data in Excel.

And the same goes for Google Sheets.

So, is there a way to use VLOOKUP without applying the formula?

Allow us to present Gigasheet.

At Gigasheet, our priority is to make the lives of spreadsheet users easier. You can use Gigasheet’s in-built Cross File VLOOKUP function to transfer data between spreadsheets. And the best part is – you don’t need to remember the VLOOKUP syntax or type in any formula.

Just upload your spreadsheets to Gigasheet and get started.

Allow me to show you how you can use VLOOKUP in Gigasheet to retrieve data from one spreadsheet and transfer it to another. I’ll use John’s example.

How to Use VLOOKUP Without Formulas

Gigasheet uses a Cross File VLookup functionality that’ll allow you to retrieve data from one spreadsheet and transfer into another without any formulas. It doesn’t let users transfer data within the same spreadsheet, like the example above. If that’s what you’re looking for, we’ve got you covered.

In case you didn’t read the above section, here’s the full context.

John is a performance marketer at a large eCommerce store with thousands of customers and millions of dollars in revenue. He has been planning to retain the customers that purchased from the eCommerce store in Q4 2022. He has a spreadsheet with 20,000 customer emails (who purchased from the store in Q4 2022) and their total purchase value during that span.

He has another spreadsheet (customer database) with customer names and emails.

Now we don’t really want John to copy customer names from the company’s customer database to his Q4 2022 Transactional data spreadsheet.

Let’s automate the process using VLOOKUP in Gigasheet.

Suppose you’re John.

We’d like to teach you how to retrieve customer names from your customer database to your Q4 2022 Transactional Data spreadsheet.

Follow these steps -

• Upload both spreadsheets to Gigasheet.

• Launch both of them inside Gigasheet. First, launch the customer database spreadsheet, which comprise customer names and their respective emails -

• And here’s your Q4 2022 Transactional Data spreadsheet consisting their emails and total purchase value in Q4 2022 -

What we’re trying to achieve is – for all the emails in our Q4 2022 Transactional data spreadsheet, we want their respective customer names.

These customer names can be found in the eCommerce store’s customer database. So, we’ll retrieve customer names from the database and transfer them to our Q4 2022 transactional data spreadsheet.

Let’s do it.

• Since we want the data in our Q4 Transactional Data spreadsheet, we’ll head over to it inside Gigasheet. Select “Insert -> Cross File VLOOKUP” as displayed in the screenshot below –

Selecting “Cross File VLOOKUP” will open the following tab –

• First, select the column in Q4 2022 transactional data spreadsheet as “Email.”

• Next, select the“Customer Database” file. Make sure you have uploaded your file to Gigasheet.

Under “Select lookup column,” choose “Email.” This is the column in the customer database file.

• We didn’t select “Near Match” – as we are looking for customer names.

• We obviously need a new column in our “Q4 2022 Transactional Data” spreadsheet filled with customer names. So under “New Column(s) Content,” we selected “Customer Name.”  

P.S. - You can add multiple columns to your original spreadsheet.

And here are the results –

Impressive, isn’t it? No need to spend hours doing manual work. At the same time, you don’t need to use any syntaxes and write formulas. Gigasheet makes it easier for you. And the best part is - it’s free to use forever. You don’t have to pay a single dime unless you need access to premium features.

We want you to become a spreadsheet ninja like us. Sign up today!