How to Use VLOOKUP in Google Sheets

-

|

Introduction

VLOOKUP (Vertical lookup) in google sheets is used to search for a value in the first column of a range. For example, you could look up an employee name based on their ID.

This guide demonstrates how to use VLOOKUP in Google Sheets.

Syntax of VLOOKUP in Google Sheets

The syntax of the VLOOKUP for Google Sheets is:

VLOOKUP(search_key, range, index, [is_sorted])

search_key: the value to look up
range: This specifies the cell range to search the value specified in search_key.
index: Is the column index (number) of the value to return. The first column is 1, second 2 and so on.
is_sorted: This is a TRUE or FALSE value, indicates whether the column to be searched in the range is sorted. The default value is TRUE. In most cases, it is recommended to use FALSE.

Tip
For VLOOKUP to work as expected, your look up data must be on the first column in the range specified by range.
Advertisement

VLOOKUP in Google Sheets Example

The example I use in this guide is a real life tool I used to assign UPC codes to products in one of my businesses. There are two Google worksheets: Product Details and UPC codes. The images below shows samples of the two worksheets.

VLOOKUP in Google Sheets Examples - product details Google sheet
VLOOKUP in Google Sheets Examples - UPC codes Google Sheets

What the tool Does

If I enter a product ID from the Product Details worksheet into the Product ID column in the UPC Codes worksheet, VLOOPUP will automatically assign a UPC code to the product.

Here is the formula I used:

 =VLOOKUP(A2,'UPC Codes'!A2:B8,2,FALSE)

Here is the formula in Google Sheets.

VLOOKUP in Google Sheets Example Explained

Refer to the last 2 images above. The first image shows the VLOOKUP formula in the Product Details worksheet. For reference, here is the formula.

=VLOOKUP(A2,'UPC Codes'!A2:B8,2,FALSE)

A2 contains the search_key – the look up value.

‘UPC Codes’!A2:B8 is the range that VLOOKUP will search for the search_key.

2 is index – this is the column that VLOOKUP will return from the range if it finds the search_key.

FALSE is the is_sorted value – indicates that the range is not sorted.

Here is how the formula works:

To assign the UPC code, 222663995563 to the product, I simply copy the product ID PIP0001 to cell A2 in the UPC codes worksheet. When VLOOKUP searches the range, A2:B8 in the UPC Codes worksheet, it finds the search_key. In this example, the search_key is the product ID PIP0001. It then returns the value in cell B2, the index column.

Advertisement

How to Resolve VLOOKUP in Google Sheets “#N/A” Error

VLOOKUP in Google Sheets Example Explained

In the above example, you would have noticed that if VLOOKUP does not find a match, it returns “#N/A” error. To remove this error and add some text, use the IFERROR function.

The Syntax of the IFERROR function is

IFERROR(value, [value_if_error])

value: this is the actual result that the VLOOKUP returns if there is no error. That is, if it finds the search_key in the specified range. However, if VLOOKUP returns an error IFERROR will return [value_if_error].

So, to resolve the “#N/A” error, replace value in IFERROR formula with the VLOOKUP formula shown below:

VLOOKUP(A2,'UPC Codes'!A2:B8,2,FALSE) 

The IFERROR formula will then look like this:

=IFERROR((VLOOKUP(A2,'UPC Codes'!A2:B8,2,FALSE) ), [value_if_error])
I enclosed VLOOKUP(A2,’UPC Codes’!A2:B8,2,FALSE) in brackets, (). This is necessary to avoid error.

Next, replace [value_if_error] with a text like “UPC Code not assigned”

The IFERROR formula will now look like this:

=IFERROR((VLOOKUP(A2,'UPC Codes'!A2:B8,2,FALSE) ), "UPC Code not assigned")
VLOOKUP in Google Sheets Example Explained

Conclusion

VLOOKUP in Google Sheets is an important function that you can use to solve many business problems. I hope this guide simplifies how to use VLOOKUP in Google Sheets.

If you have any question or comment, use the “Leave a Reply” form at the end of the page. Alternatively, share your experience with VLOOKUP.

Other Helpful Guides

Additional Resources and References

LEAVE A REPLY

Please enter your comment!
Please enter your name here

FEATURED POSTS

How to Share a Folder in Windows 10 (3 Methods)

Introduction This guide demos how to share folder in windows 10. It covers 3 methods. Options...

How to Map Network Drive in Windows 10 (5 Methods)

Introduction This guide demos 5 methods to Map Network Drive in Windows 10. Options to...
How to Install Windows 10 1909 Preview Build

How to Install Windows 10 19H2 Preview Build

Introduction If you are a member of Windows 10 Insider Program you can install Windows 10 19H2 Preview Build....

How to Sign in to Windows 10 with a Microsoft Account

Introduction When you installed Windows 10 you may have created and signed in with a local account. You can...
Disable IPv6 in Windows 10

How to Disable IPv6 in Windows 10 (3 Methods)

Introduction This guide demos 3 methods to disable IPv6 in Windows 10: Disable IPv6 from...

Advertisement

TRENDING POSTS

Remote Desktop Connection

Remote Desktop Connection an Internal Error Has Occurred [Fixed]

Introduction I recently received the error message "Remote Desktop Connection an Internal Error Has Occurred". It was strange because...

Find My Samsung: Register and Use Samsung Find my Mobile

Introduction Ever wondered how you could find your Samsung phone if you lost it? Find my Samsung or Samsung...
What is the Difference Between PowerShell and CMD?

Windows Powershell vs CMD: Differences and Similarities

Introduction This short guide compares Windows PowerShell vs CMD (Windows command prompt). I will cover the history and nature...
Spotify No Longer Supports this Version of Microsoft Edge

Spotify No Longer Supports this Version of Microsoft Edge [Fixed]

Introduction When you open Spotify web player on Microsoft Edge, you may receive the error message "Spotify No Longer...
Windows 10 Won't Boot

Windows 10 Won’t Boot With Black Screen? 3 Ways to Fix It

Why Won't Windows 10 Boot Up? If your Windows 10 stops with a black screen, the first question in...

Advertisement

BEST OF ITECHGUIDES

hard drive not detected

How to Fix Hard Drive not Detected Problem

Introduction Some Windows users have experienced a situation where a hard drive is not detected after upgrading to Windows...
Dual WhatsApp account in one phone

How to Use Dual WhatsApp Accounts in One Android Phone

Introduction Most Android phones now support dual SIM. If your Android phone has 2 SIM cards you can configure...
How to Install Windows 10 1909 Preview Build

How to Install Windows 10 19H2 Preview Build

Introduction If you are a member of Windows 10 Insider Program you can install Windows 10 19H2 Preview Build....
powershell help

Get-Help Command: Syntax, Parameters, Examples

Introduction Don't know how to use a PowerShell cmdlet? PowerShell Get-Help cmdlet will give you information about any powerShell...
Websites Like Craigslist - featured

Top 5 Websites Like Craigslist to Buy and Sell Stuff Online

Looking for websites like Craigslist? In this guide I offer my top 5 Craigslist alternative websites where you can buy and sell...

RECENT POSTS

How to Enable Hyper-V in Windows 10 (3 Methods)

How to Enable Hyper-V in Windows 10 (3 Methods)

Introduction This guide demos 3 methods to enable Hyper-V in Windows 10. To install Hyper-V...
RSAT Tools in Windows 10 Explained: Plus How to Install RSAT

RSAT Tools in Windows 10 Explained: Plus How to Install RSAT

Introduction Starting from October 2018 (1809) update, RSAT Tools became part of Windows 10. From this version of Windows...

How to Enable RSAT for Active Directory in Windows 10 (3 Methods)

Introduction This guide demos 3 methods to enable Active Directory in Windows 10. It is not exactly enabling "Active...
How to Install Windows 10 1909 Preview Build

How to Install Windows 10 1909 (19H2) Preview Build

Introduction Windows 10 1909 Preview is available for Windows Insiders. Made available early September, 2019 you have to be...
How to Install RSAT in Windows 10 (3 Methods)

How to Install RSAT in Windows 10 (3 Methods)

Introduction This guide demos how to Install RSAT in Windows 10. Starting with Windows 10...

How to Share a Folder in Windows 10 (3 Methods)

Introduction This guide demos how to share folder in windows 10. It covers 3 methods. Options...
Configure Map Network Drive with Group Policy

Map Network Drive in Windows 10 with Group Policy

Introduction This guide demos how to map network drive with group policy. This guide is...

How to Map Network Drive in Windows 10 (5 Methods)

Introduction This guide demos 5 methods to Map Network Drive in Windows 10. Options to...
How to Download Windows 10 ISO with Media Creation Tool

How to Download Windows 10 ISO with Media Creation Tool

Introduction This guide demos the steps to download Windows 10 ISO. You can download Windows 10 ISO with Media...

How to Install Windows 10 from Network Boot (Via WDS Server)

Introduction This guide demos how to install Windows 10 from network boot. The steps discussed in...

Advertisement

MUST READ

Skype can't connect

How to Fix Skype Can’t Connect Error on Windows 10

Introduction If you receive "Skype Can't Connect" Error on Windows 10, it may be caused by a number of...
Schedule-a-PowerShell-Script

How to Schedule a PowerShell Script to Auto Run On a Windows Server

Introduction If you want your PowerShell script to auto run at specific intervals you need to schedule the PowerShell...
dns server not responding featured image

“DNS Server Not Responding” Error [Fixed]

What Could Cause "DNS Server Not Responding" Error Message? The error messages "DNS Server Not Responding" or "DNS...
Powershell Function

PowerShell Function: Syntax, Parameters, Examples

Introduction There are two types of PowerShell Functions, basic and advanced. A basic PowerShell Function is a list of...
What is the Difference Between PowerShell and CMD?

Windows Powershell vs CMD: Differences and Similarities

Introduction This short guide compares Windows PowerShell vs CMD (Windows command prompt). I will cover the history and nature...

By using this website you agree to accept our Privacy Policy and Terms & Conditions