Google Sheets isn’t a database. But it can be used for basic database functions. The LOOKUP function can be used to find specific information within a set of data. Related functions include VLOOKUP and HLOOKUP.
Let’s dive into how to use LOOKUP in Google Sheets.
What Does LOOKUP Do?
LOOKUP is a complicated data function. The basic format for Google Sheets LOOKUP is as follows:
In this algorithm:
- Search_key refers to the item that you’re searching for.
- Search_column refers to where you’re searching for that item.
- Return_column refers to the data that you need.
The primary issue with LOOKUP is that it can be confusing. Lookup works very cleanly when used with ID numbers, but it can perform erratically or unpredictably with text.
How to Use LOOKUP in Google Sheets
Let’s start with a very simple example. When you’re using LOOKUP in Google Sheets, you’re probably going to be using large volumes of data. But it’s easiest to understand LOOKUP in a small spreadsheet.
Here we have a list of books in the library. We know the number, title, author, and genre. We want to find out which book is number 4 in the library. So, we perform the following:
And the result we get back is “Invisible Man.” That’s the 4th book in the library. Of course, that has limited utility; we need to know the item’s number, and if we know that, we probably know the title already.
What if we want to know the author of the book instead?
It’s that simple: That’s how to LOOKUP in Google Sheets.
But realistically, you’re probably going to be looking up a specific character value — a text string. That’s where it gets a little more complicated.
Looking Up a Text String with LOOKUP
Let’s say we have the name of a book, but we need to find its author. We can do this; the “4” can easily be changed to a string instead. Remember that a string is always going to have to be encompassed in quotation marks.
Here, we wrote:
=LOOKUP(“Catcher in the Rye”,A2:A9,B2:B9);
And we were able to get “J.D. Salinger” returned. That’s the correct author of Catcher in the Rye, so our query has succeeded.
But hold on. What happens if we just enter into the field the word “Catcher”?
That gives us “Agatha Christie” which isn’t correct. And this is one of the most important issues with LOOKUP:
Results can be very unpredictable if you’re using a text lookup and you are not using the entirety of the text.
Rather than returning N/A (which it sometimes will do), Google Sheets will frequently return an incorrect value instead. Here, we can see that Agatha Christie isn’t even close to the answer — but she’s returned nevertheless.
How can we fix this? By using a wildcard.
Here, instead of typing “Catcher” we typed “Catcher%.” % means that we know that there’s more text than that — and we’re looking for anything that meets that text. And that’s important to note. If we didn’t realize that Agatha Christie didn’t write this book, we’d never catch the mistake!
And there’s another caveat: We can’t write “%Man%” to find Invisible Man. We could only write “Invisible%.” We must have the beginning of the search query — we cannot have the end of it.
At times, the query will return “N/A.” This usually means the data simply cannot be found. But otherwise, the query will often try to return whatever seems to be close enough. And its determination of what is “close enough” may not be the same as ours.
Looking Up Information in Rows with LOOKUP
Until now, we’ve been using columns of information. But it’s also possible to look up information that’s being held in rows.
Let’s imagine that our information was differently organized.
This is a bit more cumbersome. But, as you can see, it’s actually the exact same function. The only difference is that you select the first line (B1 through I1) and the second line (B2 through I2) instead of selecting the columns that you selected previously. It will work in the same way.
It’s not advised to organize data in this way, but functionally, it’s actually the same to the computer — it’s just less readable for a person.
What about VLOOKUP and HLOOKUP?
If you’re using LOOKUP, you should also be aware of the twin functions VLOOKUP and HLOOKUP, which extend its functionality. There may be times when using a VLOOKUP or HLOOKUP command will be easier or better than trying to use LOOKUP.
VLOOKUP and HLOOKUP both operate similarly to the LOOKUP feature, but there’s a notable difference — specifically, in the way that the function designates the column/row value that should be returned.
Let’s take a look at VLOOKUP.
Note that the algorithm reads as follows:
=VLOOKUP(“Catcher in the Rye”,B2:D9,2)
There are two notable differences here from the other function.
First, the data being searched is the entirety of the data, B2 through D9, rather than just the row that’s being searched for the text.
Second, we set the column that we want to return. In this case, “2.” If we changed that to “3” we would instead return the genre of Catcher in the Rye (Fiction).
So, it’s an easier way to iterate through different columns within the data set, at least in terms of writing and readability.
HLOOKUP operates in the same way but for data that’s organized horizontally.
As with LOOKUP, if you use “Catcher” it will not find the correct object. But if you use “Catcher%” it will. It all depends on your wildcards.
When would you use VLOOKUP and HLOOKUP vs LOOKUP? Effectively, they all do the same thing — but VLOOKUP and HLOOKUP provide a little more control regarding the data that you’re returning.
And there’s one other thing. At the very end of VLOOKUP and HLOOKUP, you can specify whether your data is sorted or not. You can enter TRUE if it’s sorted and FALSE if it isn’t. You should remember this. If your data isn’t being returned properly, it’s likely because of this issue.
What Are the Limitations of LOOKUP?
By now, it’s become apparent that while LOOKUP is a very useful feature, it’s also a limited one.
When you use LOOKUP, you should be aware:
- You should always check and double-check your data to validate it. LOOKUP can “silently fail” or give incorrect results. This is especially true because it will return only one value — so if you’re looking for “Bob” and have two “Bob”s, you might not get the one you want.
- Your data should always be sorted. If your data isn’t sorted before you start using LOOKUP, then the functionality may be limited or confusing.
- You should consider using VLOOKUP or HLOOKUP. These can provide better levels of granular control over the data that you get.
Realistically, Google Sheets isn’t designed to be a replacement for a real database. There’s no structured query language (SQL) for Google Sheets, though Google Sheets can easily be imported into a true database. So, your expectations for database-like features should be fairly limited.
But even so, LOOKUP can make your job easier. In our example, we have only a few books to sort through. But in your example, you could have hundreds or even thousands of records to check.
You now know how to use LOOKUP in Google Sheets!
Want to become a Google Sheets expert? Check out our roundup of the top Google Sheets online courses!