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
- search_key = Data to search the first column for (can be text or a number).
- range = Complete data table (array or range reference). Only the first column of which will be searched using the search_key.
- index = Column number of the data you want to return (first column = 1).
- [is_sorted] = Optional setting telling the function if the searched column is sorted. This is TRUE by default but you’ll want it to be FALSE in nearly every use.
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:
A | B | C | |
1 | Item | Quantity | Price |
2 | Apples | 7 | $4.42 |
3 | Bananas | 7 | $3.34 |
4 | Carrots | 3 | $2.90 |
5 | Mushrooms | 12 | $5.29 |
6 | Onions | 1 | $1.38 |
7 | Chickens | 1 | $10.62 |
8 | Milk | 1 | $2.71 |
9 | Eggs | 12 | $3.64 |
10 | Cheese | 1 | $5.69 |
11 | Butter | 1 | $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:
To find the price of Carrots ($2.90) you can use:
A | B | C | |
1 | First | Last | Sales |
2 | Dwight | Schrute | 10,000 |
3 | Jim | Halpert | 8,000 |
4 | Stanley | Hudson | 7,000 |
5 | Phyllis | Vance | 6,000 |
6 | Andy | Bernard | 4,000 |
7 | |||
8 | Stanley | ||
9 |
FREE RESOURCE
Google Sheets Cheat Sheet
12 exclusive tips to make user-friendly sheets from today:
You'll get updates from me with an easy-to-find "unsubscribe" link.