|

FEATURED POSTS

How to Change Outlook Password in 3 Different Ways

How to Change Outlook Password in 2 Easy Steps

Introduction Before you change your Outlook Password you have to first change it with your email provider. The reason...
spotify web player not working

Spotify Web Player Not Working [Fixed]

Introduction Spotify Web Player may stop working for you with the following error messages: "Spotify Web Player an Error...
DISM.exe /Online /Cleanup-Image /Restorehealth

DISM.exe /Online /Cleanup-Image /Restorehealth Explained

What is DISM.EXE /Online /Cleanup-image /RestoreHealth? "DISM.exe /Online /Cleanup-Image /Restorehealth" is a DISM command that repairs issue with the...
DHCP Relay agent

DHCP Relay Agent: Configuration in Windows Server 2016

What is a DHCP Relay Agent? A DHCP Relay Agent allows DHCP clients in a different network subnet to...
ForEach-Powershell

PowerShell ForEach: Syntax, Parameters, Examples

What is PowerShell ForEach? PowerShell ForEach (ForEach PowerShell) is a PowerShell construct used in iterating through values in a...

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...
PowerShell vs CMD

Powershell vs CMD: Differences and Similarities Compared

Introduction This short guide compares PowerShell vs CMD (Windows command prompt). I will cover the history and nature of...
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...

BEST OF ITECHGUIDES

RAID 50

RAID 50 vs RAID 10: Benefits and Disadvantages Compared

What is RAID 50 vs RAID 10? RAID 50 stripes two RAID 5 arrays while RAID 10 stripes two...
FTP and SFTP ports

FTP and FTP Port, SFTP and SFTP Port: Quick Reference

Introduction FTP and SFTP are two protocols for transferring files between a server and a client computer. FTP port...
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...
How to Change Outlook Password in 3 Different Ways

How to Change Outlook Password in 2 Easy Steps

Introduction Before you change your Outlook Password you have to first change it with your email provider. The reason...
DISM Host Servicing Process (DismHost.exe)

Dism Host Servicing Process (DismHost.exe), a Malware?

Introduction There are conflicting information about Dism Host Servicing Process (DismHost.exe). Some say it is a genuine Windows process,...

RECENT POSTS

how to merge cells in excel

How to Merge Cells in Excel in 2 Easy Ways

Introduction You can merge two Cells in Excel using CONCATENATE function or the “&” (ampersand) operator. Though Excel has...
How to Make a Pivot Table in Google Sheets

How to Make a Pivot Table in Google Sheets

Introduction You can make a Pivot Table in Google Sheets to simplify analysis of complex data. A Pivot Table...
how to make Pivot Table

How to Make a Pivot Table in Excel

Introduction A Pivot Table allows you to analyze, summarize and calculate large data to help find relationships. With a...
RAID 3 (Redundant Array of Independent Disks) Explained

RAID 3 (Redundant Array of Independent Disks) Explained

What is RAID 3? RAID 3 is a RAID implementation that uses striping with a dedicated parity disk....
RAID 5 vs RAID 6

RAID 5 vs RAID 6: Differences, Benefits and Disadvantages

What is RAID 5 vs RAID 6? RAID 5 and RAID 6 uses striping with distributed parity technique. However,...
concatenate excel

Concatenate in Excel: How to Concatenate Columns and Strings

What is Concatenate in Excel? Concatenate in Excel is joining two strings into one continuous string. You can join...

How to Add in Excel (Excel Sum) with Examples

Introduction There are different ways to add numbers in Excel. You could simply select the cells containing the data....
Excel Count

Excel Count: How to Count in Excel With Examples

Introduction Excel COUNT Function is used for counting items in a worksheet. Excel COUNT also has the conditional function,...
powershell.exe -command

Powershell.exe Command: Syntax, Parameters and Examples

Introduction You may be wondering why write on Powershell.exe Command. Are there special commands for Powershell.exe? Yes! When you...
how to move columns in excel

How to Move Columns to Rows and Rows to Columns in Excel

Introduction If you receive some Excel data in columns, you can easily move the columns to rows in Excel...

MUST READ

Powershell For Loop Explained: Syntax and Examples

Introduction "Powershell For Loop" (or Statement) is a construct that is used to run a command in a...

Change User Name and Rename User Folder in Windows 10

Introduction There are so many reasons why you may want to change user name in Windows 10 or rename...
How to Make a Pivot Table in Google Sheets

How to Make a Pivot Table in Google Sheets

Introduction You can make a Pivot Table in Google Sheets to simplify analysis of complex data. A Pivot Table...
DISM.exe /Online /Cleanup-Image /Restorehealth

DISM.exe /Online /Cleanup-Image /Restorehealth Explained

What is DISM.EXE /Online /Cleanup-image /RestoreHealth? "DISM.exe /Online /Cleanup-Image /Restorehealth" is a DISM command that repairs issue with the...
RAID 3 (Redundant Array of Independent Disks) Explained

RAID 3 (Redundant Array of Independent Disks) Explained

What is RAID 3? RAID 3 is a RAID implementation that uses striping with a dedicated parity disk....

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

YOU MAY ALSO LIKE:

LEAVE A REPLY

Please enter your comment!
Please enter your name here

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