As far as I’m concerned the Lookup function in Google Sheets seems little complicated in usage. Why I’m telling this because if you use this function without proper knowledge, it can invite wrong results. To understand this, you should first know the use of Lookup formula in detail. Be careful in using Lookup function in Google Sheets.
So let’s learn how to use Lookup function in Google Sheets. Also, you can learn here how Lookup function is different from Vlookup. Yup! you can also find A Lookup Vs. Vlookup comparison below. Here is that advanced Lookup usage tutorial that you were looking for!
You May Like: How to Use Vlookup Across Different Sheet Tabs in Google Sheets
Syntax:
LOOKUP(search_key, search_range|search_result_array, [result_range])
The syntax may little bit confusing to you. I’ll explain it in the example section below. But let me tell you one thing.
You can use Google Sheets Lookup function in two different ways.
One is the search range and searches result range method and the second one is the search result array method. The former is very simple and easy to use compared to the latter.
The Lookup function may only work properly in Google Sheets if it can meet the following condition.
The data in search range or search result array should be sorted.
You may have doubt what is this search range and search result array. In the real sense both are same but a slight difference in use. Below I have detailed the former under method 1 and the latter under method 2.
Examples of Lookup Function in Google Sheets
Sample Data:
Important Note:
Use Lookup function in Google Sheets to look through a sorted row or column.
What is this so-called sorted row or column?
The below example (method 1) is based on the sorted column. In method 2, I have detailed sorted column (vertical lookup) as well as the sorted row (horizontal lookup).
Method 1: Usage of LOOKUP in Search Range and Search Result Range Method
Just take a look at the above sample data. You can see that the first column is sorted. It’s important to sort the column, then only we can use Lookup. The below is the syntax for the method 1 use.
LOOKUP(search_key, search_range, result_range)
See one formula based on the above sample data to understand this.
Formula:
=lookup("Product 4",A2:A7,G2:G7)
Result: 1418
Here “Product 4” is the search key, A2: A7 is the search range and G2: G7 is the result range.
This formula checks the search key “Product 4” in column range A2: A7 and returns the result from the corresponding row in the column range G2: G7.
This method is very simple to understand and use. Now to the complex part.
Method 2: Usage of LOOKUP in Search Result Array Method
See another way of using Lookup formula in Google Sheets.
The syntax for method 2 use.
LOOKUP(search_key, search_result_array)
Here there is only search key and search result array. The search result array means the data range.
In this type of LOOKUP, the first row or column is searched and a value is returned from the last row or last column in the array.
Example:
First column search and return the value from the last column.
The formula here looks for the search key “Product 4” in sorted column A and returns the value from Column D from the corresponding row.
Example:
First row search and return the value from the last row.
Before going to this example, you should know what is “first-row search” and “first-column search”.
1. If the range (here A2: D7) contains an equal or more number of rows than columns, then the search will be from top to bottom over the first column in the range (vertical lookup). The above example matches this condition since there are 6 rows and 4 columns.
2. If the range (search result array) provided contains more columns than rows, then the search will be from left to right over the first row in the range (horizontal lookup).
See that example.
Here what I want to return is the value in G7 matching the search key in the first column “Product 4”. I was expecting the result of 1418 but the formula returned the value “Product 6”. Why?
Here the Lookup formula automatically behaves like horizontal lookup up since the number of columns is more than the number of rows.
The formula looks for the search key “Product 4” in the first row. Since it’s not there, the formula returns the last row value in the first column. Because if the search key is not found the formula would behave like this.
If the search_key is 5 and your lookup row or column in your dataset contains the numbers 3, 4, 6 then 4 will be used for the lookup.
In the above example “Product 4” is the search key. So the formula looks for this value in the first row and finds the nearest match in A2, that’s “Product 1” and returned the last row value in that column.
As I told you, the above result is wrong. To properly work this formula, the data would be as below.
Now time to go to the comparison part of Vlookup Vs Lookup.
Lookup and Vlookup Comparison in Google Sheets
If you are using Vlookup, you can use the formula as below. Refer the first screenshot to understand the cell reference used in this formula.
=Vlookup("Product 4",A2:G7,7,FALSE)
I have a detailed tutorial on Vlookup. You can search on this site to find that. Still, for comparison purpose with Lookup see the syntax below.
VLOOKUP(search_key, range, index, [is_sorted])
In both Lookup and Vlookup, the first syntax element is the search key. The last argument “TRUE” to “FALSE” indicates whether the first column in the range is sorted.
Even though the first column is containing the sorted value, I’ve used here FALSE because the value in the first column is text. If the first column contains numeric values and sorted, you can use “TRUE” to improve the performance of the function.
Reverse lookup is possible with Lookup similar to Index Match. You can use the above method 1 for this purpose. But it’s not possible using Vlookup. Also, the Lookup method 2 above doesn’t support reverse lookup.
Similar: Case Sensitive Reverse Vlookup in Google Sheets
Example to Reverse Lookup Using Lookup Function in Google Sheets
In this example, the search range is column E and the result range is Column B. I’ve used the above method 1 here. This type of reverse lookup is not possible using Vlookup.
Can we Use Lookup Function in Google Sheets in Array Formula?
The answer is Yes. Similar to the use of Vlookup in Array, you can use Lookup also in an array to lookup multiple search keys.
That’s all. Any query regarding this advanced use of Lookup in Google Sheets, please do comment.
