Easy Tutorial for Google Sheets LOOKUP (3 Examples)

If you have ever been in a situation where you want to pull up data from your spreadsheet, then you might have needed a function like the LOOKUP function in Google Sheets. 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 the Google Sheets LOOKUP function.

What Does LOOKUP Do?

LOOKUP is a complicated data function. The basic format for Google Sheets LOOKUP is as follows:

=LOOKUP(‘search_key’,’search_column’,’return_column’);

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 spreadsheet 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 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:

1. Type the lookup formula in the cell
2. Select the cell that has the search key, then type a comma
3. Select the first column that contains the search range, then type a comma after
4. Select the column for the result range and close the brackets

=LOOKUP(4,A2:A9,B2:B9)

5. Hit Enter to get the results.

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.

If we were to use a search array instead of a search range, then we would not need to specify the result range since the lookup function will use the search key in the first column to return the corresponding value in the last column. In this case the syntax would be:

=LOOKUP(search_key, search_result_array)

In our lookup table above, if we were to use the search result array, then the search key would be in the first column for the book title, and the result would come from the column for genres.

What if we want to know the author of the book instead? We’d instead use the formula:

=LOOKUP(4,A2:A9,C2:C9)

It’s that simple: That’s how to LOOKUP in Google Sheets.

But realistically, you’re probably 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 will always 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 the word “Catcher” into the field?

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 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 the 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 for the computer — it’s just less readable for a person.

How to Use the LOOKUP Function in Google Sheets in an Array Formula

The array formula lets you perform multiple lookups at once instead of putting the LOOKUP formula in each cell one by one. You can also use it when you want to use multiple search keys.

The search key can therefore be a range instead of just one cell value.

For example, in our sheets below, let’s say we want to find the price of each shoe.

1. First, we would create a separate Google Sheets lookup table for the price of each shoe.

2. In the cell for the shoe price, we will input the formula:

=Arrayformula(LOOKUP(‘search_key’,’search_column’,’return_column’))

Which translates to the following image:

3. If you press Enter you will notice that the entire column for shoe prices is filled with values.

One thing to note is that your values in the lookup table will not be accurate if you did not sort the original table first. Otherwise, you will get the corresponding price for each shoe all at once, thanks to the array formula.

What about VLOOKUP and HLOOKUP Functions?

If you’re using  Google spreadsheet 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 how the function designates the column/row value that should be returned. One works as a vertical lookup, while the other works as a horizontal lookup.

Let’s take a look at VLOOKUP.

Note that the algorithm for the VLOOKUP formula 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 works as a horizontal lookup for data that is organized horizontally.

As with Google 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 HLOOKUP and VLOOKUP 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. In Google Sheets LOOKUP vs VLOOKUP formula, lookup comes on top since it can work on both rows and columns, while VLOOKUP only works as a vertical lookup with columns.

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.

Related: VLOOKUP From Another Sheet

What Are the Limitations of LOOKUP?

By now, it’s become apparent that while LOOKUP sheets 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 from a sorted row or column. 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.

Key Tips When Using LOOKUP in Google Sheets

There are a number of tips you should follow to use the lookup formula in Google Sheets as accurately as possible. Here are a few:

• Use the ascending order when sorting your data for the best results
• Make sure that the search range does not go further than the result range, otherwise the Google Sheet lookup function will likely return an error.
• If the search range and result range are not aligned, you are also likely to get inaccurate results.
• If you use a lookup value that is not in the search range, then the Google Sheets lookup function will look for the value closest to the search key. If the values are numbers, it will use the lowest number closest to the search key.

Conclusion

Using lookup is a straightforward way to find information on your Google Sheets spreadsheet. You can either use the search result range or the search array way. You now know how to use Google Sheets LOOKUP functions!

Want to become a Google Sheets expert? Check out our roundup of the top Google Sheets online courses!

Related:

• How to Add Subscript and Superscript in Google Sheets
• How to Insert BULLET POINTS in Google Sheets
• How to Indent Text in Google Sheets
• How to Insert Check Box (Tick Box) in Google Sheets
• How to Show Negative Numbers in Red in Google Sheets
• How to Clear Formatting in Google Sheets