- \n
- It only works if the data in the column or row is sorted. Luckily you can easily fix that with the <\/span>SORT function<\/span><\/a>.<\/span><\/li>\n
- While VLOOKUP only searches vertically (in a column) and HLOOKUP only searches horizontally (in a row), LOOKUP formula can search both.<\/span><\/li>\n<\/ul>\n
This function\u2019s syntax is flexible and enables two different approaches to use it.<\/span><\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Approach 1: Providing a search range and a distinct result range<\/span><\/h2>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
LOOKUP(search_key, search_range, result_range)<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
This approach looks up a key in a search range (e.g. A3: A12) and returns a value from a result range in the same position (e.g. F3: F12).<\/span><\/p>\n
- \n
- search_key: the value the function will search for.<\/span><\/li>\n
- search_range: where the function will search for the search_key.<\/span><\/li>\n
- result_range: from where the function will pull the result.<\/span><\/li>\n<\/ul>\n
[\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Approach 2: Unified search and result range<\/span><\/h2>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
=LOOKUP(search_key, search_result_array)<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
This option allows you to search for a key in the first row or column to return a result from the last row or column in the array.<\/span><\/p>\n
- \n
- search_result_array: the range you want to return a result from (e.g. A3: G15).\u00a0<\/span><\/li>\n
- \n
- It will search the first row or column in the array and pull the result from the last row or column.<\/span><\/li>\n
- If the array contains more (or the same number of) columns than rows, the search will work vertically, searching the first column and pulling a result from the last column.<\/span><\/li>\n
- If the array contains more rows than columns, the lookup will run horizontally within the rows.<\/span><\/li>\n<\/ul>\n<\/ul>\n
[\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
How to use Google Sheets LOOKUP Formula<\/span><\/h2>\n
I\u2019ve applied the LOOKUP function to the data below and provided a few different examples of how to use it.<\/span><\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/GOOGLE-SHEETS-LOOKUP-FORMULA-1.png” alt=”GOOGLE SHEETS LOOKUP FORMULA 1″ title_text=”GOOGLE SHEETS LOOKUP FORMULA 1″ align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ width=”640px” max_width=”640px” height=”440px” max_height=”440px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
How to apply LOOKUP to a column<\/span><\/h2>\n
It\u2019s worth noting that LOOKUP, unlike VLOOKUP and HLOOKUP, can return values placed before the lookup column. Take a look at rows 4 and 5 to see examples of this.<\/span><\/p>\n
You will also see that for the search_result_array option, the results come from the last row or column in the range. You can see this in the examples in rows 3 and 6. If you need to fetch results from a column or row in the middle of the range, you may have to use the <\/span>VLOOKUP<\/span><\/a> or <\/span>HLOOKUP<\/span><\/a> formulas.<\/span><\/p>\n
One underlying assumption in all of the examples, from rows 2 through to 6, is that the columns are sorted in ascending order.<\/span><\/p>\n
What if they data is not sorted in ascending order? How does LOOKUP formula behave in this instance?<\/span><\/p>\n
I\u2019ve experimented with that in row 7. You\u2019ll notice that the Salary column is not sorted. While the formula itself has returned a value, it\u2019s incorrect. For the LOOKUP formula to function as expected, you need the lookup column to be sorted in ascending order.<\/span><\/p>\n
Sometimes you might find that the lookup value does not match any of the values in the lookup column or row (the last example in the screenshot above). In this scenario, the formula will return the closest value to a match. This may not always make practical sense, and you might need an exact match. This is where this formula falls short, and you need to rely upon VLOOKUP and HLOOKUP for exact matching capabilities. Unlike those functions, LOOKUP function does not have an option for approximate vs. exact matches.<\/span><\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
How to apply LOOKUP to a row<\/span><\/h2>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/GOOGLE-SHEETS-LOOKUP-FORMULA-2.png” alt=”GOOGLE SHEETS LOOKUP FORMULA 2″ title_text=”GOOGLE SHEETS LOOKUP FORMULA 2″ align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ width=”640px” max_width=”640px” height=”440px” max_height=”440px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Here the same logic applies, but you select ranges as rows instead of columns. You can use either syntax option, and just like in the previous example, you must sort your data in ascending order beforehand.<\/span><\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
LOOKUP Formula: Can it misbehave?<\/h4>\n
So far I have been experimenting with equal-height columns or equal-width rows. What happens if I input a different sized reference to the <\/span>search_range<\/b> and the <\/span>result_range<\/b>?<\/span><\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/GOOGLE-SHEETS-LOOKUP-FORMULA-3.png” alt=”GOOGLE SHEETS LOOKUP FORMULA 3″ title_text=”GOOGLE SHEETS LOOKUP FORMULA 3″ align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ width=”640px” max_width=”640px” height=”440px” max_height=”440px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
You can see from the examples that different sized ranges do not cause an issue as long as the relative position of the matched value from the <\/span>search_range<\/b> does not exceed the size of the <\/span>result_range<\/b>.<\/span><\/p>\n
I only get an error in the last example, where the result range is not large enough to match the row number of the search key in the search_range.<\/span><\/p>\n
In the second and third examples, you can see that I don\u2019t get an error, but the answers are wrong. This is because the search_range and result_range do not align.<\/span><\/p>\n
In the second example, the function determines that the item it\u2019s searching for is in the fifth row of the search_range. It then pulls the result from the fifth row of the result_range.<\/span><\/p>\n
Because the ranges are not aligned, the result that the function fetches is wrong. This can be seen in the third example as well.<\/span>\u00a0<\/span><\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
\u00a0Comparison of LOOKUP, VLOOKUP, and HLOOKUP<\/h2>\n
I\u2019ve explained some of the differences between the LOOKUP function and its counterparts, the VLOOKUP and HLOOKUP functions. Now that you\u2019ve seen some examples and know how LOOKUP works, let’s look at the advantages, and disadvantages of each.<\/span><\/p>\n
- If the array contains more (or the same number of) columns than rows, the search will work vertically, searching the first column and pulling a result from the last column.<\/span><\/li>\n
- It will search the first row or column in the array and pull the result from the last row or column.<\/span><\/li>\n
- search_range: where the function will search for the search_key.<\/span><\/li>\n
- While VLOOKUP only searches vertically (in a column) and HLOOKUP only searches horizontally (in a row), LOOKUP formula can search both.<\/span><\/li>\n<\/ul>\n
![\"LOOKUP](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/Lookup1.png\")
\n\nIt is worth noting that the LOOKUP formula, unlike the VLOOKUP and HLOOKUP formulas, can return values before the lookup column. For instance, the cases in rows 4 and 5 illustrate this concept.\n\nAlso, please observe that for the ![\"LOOKUP](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/lookup2.png\")
\n![\"LOOKUP](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/lookup3.png\")
\n\nWe can deduce from the examples that, the different sized ranges do not really cause an issue. As long as the relative position of the matched value from