Being able to conditionally look for data is one of the pillars of well-done spreadsheets.
If you've ever wondered about the Google Sheets LOOKUP formula in all its variants, wonder no more. After reading this article, you’ll have no doubts on setting up your lookup table and how to use this powerful function.
Quickstart
Here, I’ll present the syntax, key features, and drawbacks of this function. You are welcome to read more or just skip the quickstart section as I delve into everything in detail.
The naked formula:
=LOOKUP(searchKey, searchRange, resultRange)
Practical example
=LOOKUP(“My Text”, A2:A21, B2:B21)
LOOKUP searches for “My Text” within A2 and A21. If found, it returns the content or its row within B2 and B21.
The drawback is that it only works if the search range is sorted alphabetically; otherwise, it may return inexact data or an error.
How To Use the LOOKUP Function In Google Sheets
Like all the LOOKUP functions, the good old LOOKUP formula allows you to retrieve values from a table based on your search criteria. For example, when you know the name of a product but don’t know its price available on a different table. The first time I used a LOOKUP formula was when creating my own calories and macros calculator.
I had a table with food and their nutritional facts. By using the LOOKUP function, I could calculate their calories based on the amount I was planning to eat (and a bunch of additional formulas).
The LOOKUP function is the most strict of them all. Although it has its own benefits, its major problem is that it only works reliably with sorted tables, which means it may return incorrect values when either the column with the search values or the column with the results is not sorted (more on this later, though).
LOOKUP Syntax
Here’s the simplest syntax of the LOOKUP function in Google Sheets:
=LOOKUP(search_key, search_range)
Or, with a more practical example:
=LOOKUP(F7, A2:D21)
This way to write the LOOKUP formula is very handy as you only input two parameters because the range provided indicates two distinct instructions.
Google Sheets VLOOKUP won’t look for content in a range, but rather in columns. In fact, whenever you use a range, you tell Google Sheets that it must search for the value in column A and return the content of D in the row where the term is found.
In other words, if we have the table below, where F7 contains “Vibro-Magnetic Pulse Generators” (the last entry in the table), it will be searched within column A (more precisely A2:A21). If found, the result will be the content of column D (again D2:D21).
The image above shows two different examples, both search for the same value “Vibro-Magnetic Pulse Generators” but the range is different, and in the first case, it will return the contact in column D, while in the second case, it shows the items available in column C because the range in the second case is A2:C21 and column C contains the items in the inventory.
In the event that you want to use more arguments in your formula, you can use a different format:
=LOOKUP(search_key, search_range, result_range)
Or more practically:
=LOOKUP(F7, A2:A21, B2:B21)
In this case, we search for “Vibro-Magnetic Pulse Generators” in column A (A2:A21) and return the content of B (B2:B21) in the corresponding row:
This also overcomes some limitations, for instance, your search criteria are not before your search result.
Finally, even if VLOOKUP won’t allow you to search within multiple columns, you can overcome that limitation through array search, here’s an example below:
=LOOKUP(search_key, search_result_array, result_range)
Here are two practical examples in case you are not familiar with array search in formulas:
=LOOKUP(F14, {A2:A21, D2:D21}, B2:B21)
=LOOKUP(F14, {A2:A21, D2:D21} , C2:C21)
In this case, we still look for the last entry of the table “Vibro-Magnetic Pulse Generators,” which is the content of F14, and return either the price (first example) or the inventory in the second example. The result is always specified at the end of the formula, respectively column B and C of the examples above.
The array part of the formula is the one within curly braces. This allows us to look for the content of F14 under either column A or column D (range from 2 to 21), which is respectively the name of the item or the email of the seller.
{A2:A21, D2:D21}
In fact, we can now search for both terms and retrieve either the price or the inventory (based on the formulas above). This is very useful as it won’t limit us to only one particular type of information to look for. You can see this in action in the example below where the term is now the email address of the last entry of the table “
The result in the first formula is the price, and in the second formula, is the items in inventory.
Pitfalls of the LOOKUP function
There is not much to get wrong here, but you need to be very aware of them when using the LOOKUP function as if you forget about them and blindly rely on the results, you may stumble across unpleasant surprises.
According to Google Sheets’ documentation for the LOOKUP function, you need to have the data in the “search_range” or the “search_result_array” (second example). That’s pretty much the problem however; you need to be aware that if you look for content on a “search_result_array,” you must have sorted the values in the column where the data is, in other words, if I sort by email address, but I sort the table by Item instead of contact, the results will be incorrect:
In the example above, the table is sorted by Item, and when looking for the email address, the results are incorrect, in fact, the correct values should be “$5,700” and “0” (first row of the table), but the formulas return respectively “$4,200” and “83” which correspond to the last entry in the table, which corresponds to a different email address.
This happens because when LOOKUP doesn’t find the term in its search, it will return the next close in alphabetical or ascending order. In this case, we look for an item that begins with “z,” which is in the first row, and the item with the “x,” which is the letter just before “z” in our list.
Conclusion
To conclude, the LOOKUP formula is a versatile way to look for data as long as the data that you are looking into is sorted. Its benefits to be able to look for data in multiple columns are also its big pitfall as unless all the values in all the columns in the array are sorted, it may not work as expected.
You can take a look at the other ways to lookup for data as they are all available in the right side menu (or at the bottom of the page if you are on a small device) and compare what’s best for you.