Advanced Tips for Using the LOOKUP Function in Google Sheets

Published on

The LOOKUP function requires a sorted range to work correctly. If the data is not sorted, use the SORT function in combination with it.

We will use a minimal sample dataset to help you clearly understand all aspects of using the LOOKUP function in Google Sheets.

Syntax

The syntax is as follows:

LOOKUP(search_key, search_range | search_result_array, [result_range])

You can use LOOKUP with either two arguments or three arguments:

Two Arguments: search_key and search_result_array

In this method:

  • The data must be sorted in ascending order.
  • It looks up vertically (top to bottom in the first column) when the number of rows is greater than or equal to the number of columns in the search_result_array; otherwise, it looks up horizontally (left to right in the first row).
  • If the search key is not found in the search_result_array, the formula will look up the largest value in the range that is less than the search key.
  • It will return the result from the last column (in vertical lookup) or the last row (in horizontal lookup) in the provided range.
  • When there are duplicates of search keys, the last occurrence of the search key will be considered.

Three Arguments: search_key, search_range, and result_range (similar to what you may see in XLOOKUP)

All the points above are applicable here as well, except the second point, which does not apply since we are using two individual arrays.

Using the LOOKUP Function with Two Arguments: Examples

Let’s use minimalistic data for this test to help you better understand the function.

The sample data is in range A1:B, where column A contains delivery dates and column B contains quantities delivered.

Enter 18/10/2024 in cell D2 and the following formula in E2:

=LOOKUP(D2, A:B)

This will return 100 since the date 18/10/2024 is not available in the first column. The next lowest available date is 16/10/2024, so the formula returns 100 from the same row in the last column.

LOOKUP function performing a vertical search with more rows than columns

Change the date in D2 to 20/10/2024. The formula returns 80 because the search key occurs twice, and it considers the last occurrence.

If the data aligns horizontally in the range 1:2, you can use the following formula to look up the search key in cell B5 in the first row and return the value from the last row, which is the second row.

=LOOKUP(B5, 1:2)
LOOKUP function performing a horizontal search with more columns than rows

Using the LOOKUP Function with Three Arguments: Examples

As mentioned earlier, the LOOKUP function with three arguments strictly follows all the features of the two-argument formulas described above.

Let’s start with the LOOKUP function in vertical data:

=LOOKUP(D2, A:A, B:B)

This will return 100 when the search key is 18/10/24 in cell D2 and 80 when the search key is 20/10/24.

For horizontal data, try these formulas:

=LOOKUP(B5, 1:1, 2:2) // returns 100 when B5 is 18/10/24, 80 when B5 is 20/10/24

Compared to the two-argument LOOKUP, the three-argument version offers the flexibility to return results from any specified column (in vertical lookup) or row (in horizontal lookup).

Combining SORT and LOOKUP Functions

The best way to use LOOKUP is to work with sorted data. However, if you don’t want to alter the original data but still perform a lookup, you can use functions like VLOOKUP, XLOOKUP, or HLOOKUP. Alternatively, you can use the SORT function in combination with LOOKUP.

Here is the data in A1:B:

ItemQty
Orange7
Apple3
Orange6
Mango2
Banana1

Here’s how to correctly apply the LOOKUP function to unsorted data in Google Sheets:

Two-Argument LOOKUP Formula:

=LOOKUP("Orange", SORT(A2:B, 1, 1)) // returns 6

In this formula, the search_result_array is sorted within LOOKUP by the first column in ascending order.

Three-Argument LOOKUP Formula:

=LOOKUP("Orange", SORT(A2:A, 1, 1), SORT(B2:B, A2:A, 1)) // returns 6

Here:

  • The search_range is SORT(A2:A, 1, TRUE), which sorts the items in column A in ascending order.
  • The result_range is SORT(B2:B, A2:A, TRUE), which sorts column B based on the values in column A.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.