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

Windows 10 store not Working

3 Quick Ways to Fix Windows 10 Store if it Won’t Open

Introduction Is Windows 10 store not working in your computer? If Windows 10 store is not working it may...
disable cortana windows 10 featured

How to Disable Cortana in Windows 10 (2 Methods)

Introduction Some Windows 10 users may not like Cortana. Solution? Disable Cortana. You are probably reading this because you...
Windows 10 not booting after update

How to Fix Windows 10 Boot Problem After Windows Update

If you experience Windows 10 not booting after an update, it is likely that the update corrupted the boot files.
how to change wifi password

How to Change Wifi Password for Any Wifi Router

Introduction To change your Wifi password (PIN), you need the IP address and admin login for the router. Once...
Active Directory Domain Services

Active Directory Domain Services: Installation & Configuration

What is Active Directory Domain Services? Active Directory Domain Services (AD DS) is at the core of Microsoft's Directory...

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

bad pool header blue screen error

How to Fix Bad Pool Header Blue Screen Error in Windows 10

Introduction Bad Pool Header error is related to memory allocation issue in Windows 10. Bad Pool Header error usually...
The Requested Operation Requires Elevation

How to Fix “The Requested Operation Requires Elevation” Error

Introduction "The Requested Operation Requires Elevation" error may be caused by a number of reasons. Here are some of...
Configure Group Policy for Windows Updates Server 2016 (WSUS Server 2016)

How to Configure Group Policy for WSUS in Windows Server 2016

Introduction This guide demos how to configure Group Policy for Windows Updates Server 2016 (WSUS Server 2016).
powershell add ad computers to array

How to Add AD Computers to a Powershell Array

Introduction There is one way to add AD Computers to a PowerShell array. A PowerShell array is a data...
oooops, something went wrong. reload

How to Fix “Oooops something went wrong. reload” Spotify Error

Introduction If you receive "Oooops something went wrong. reload" Spotify error, do not panic. The fix is simpler than...

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