Spreadsheets can be BIG. Lots of columns and LOTS of rows.

In this lesson you’re going to learn how to quickly and easily locate data in a spreadsheet so you don’t have to do a manual search ever again.

You'll be using the famous VLOOKUP function.

And once you know how to use it, you’ll be amazed at how often it comes in handy.

VLOOKUP

The VLOOKUP function searches for a piece of data in the first column of a table and, when it finds the row, returns data from a nominated column in that row.

Syntax

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

When [is_sorted] is FALSE only the first exact match is returned with an #N/A error returned if an exact match of the search_key isn’t found.

When [is_sorted] is TRUE, the nearest match (less than or equal to search_key) is returned. An #N/A error is returned if all values in the search column are greater than the search_key.

Other Errors

You will receive a #VALUE! error if the nominated index is less than 1.

You will receive a #REF! error if the nominated index is greater than the number of columns in the range.

Example

Here are some grocery items:

ABC
1ItemQuantityPrice
2Apples7$4.42
3Bananas7$3.34
4Carrots3$2.90
5Mushrooms12$5.29
6Onions1$1.38
7Chickens1$10.62
8Milk1$2.71
9Eggs12$3.64
10Cheese1$5.69
11Butter1$4.41

It’s relatively easy to just look at this table and interpret it. Imagine if it was 1000 items instead of 10. Then you’d need VLOOKUP.

To find the quantity of Mushrooms (12) you can use:

=VLOOKUP("Mushrooms",A2:C11,2,FALSE)

To find the price of Carrots ($2.90) you can use:

=VLOOKUP("Carrots",A2:C11,3,FALSE)
Looking Up Data Exercises
Enter a formula for each question in the formula bar for the spreadsheet below to access the next lesson.
1. Last Name
In cell B8, enter a formula that finds Stanley's last name in the table.
2. Sales
In cell C8, enter a formula that finds Stanley's sales in the table.
ABC
1FirstLastSales
2DwightSchrute10,000
3JimHalpert8,000
4StanleyHudson7,000
5PhyllisVance6,000
6AndyBernard4,000
7
8Stanley
9
Finish Course

FREE RESOURCE

Google Sheets Cheat Sheet

Learn 12 tips and tactics that will make your sheets more beautiful and user-friendly from today.

Google Sheets Cheat Sheet

By entering your email address you agree to get email updates from me. I'll respect your privacy and you can unsubscribe at any time.