[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Syntax<\/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”]=VLOOKUP(search_key, range, index, [is_sorted])[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
- \n
- search_key<\/strong> \u2013 is the value that the VLOOKUP function uses to search.<\/li>\n
- range<\/strong> \u2013 is the reference to the range of cells that we are performing a search on. Google Sheets application looks for the key in the first column of the range.<\/li>\n
- index<\/strong> \u2013 this is the column index of the cell within the range, whose value the function returns. The first column\u2019s index within the range<\/strong> is 1, the second in the range<\/strong> is 2 and so on. For example, if we input 3 against this parameter, the VLOOKUP function returns the cell value from the third column and the row in which it finds the search_key<\/strong>.<\/li>\n
- [is_sorted]<\/strong> \u2013 is an optional parameter which is TRUE by default. This indicates whether the first column in the range<\/strong> is sorted in ascending order or not. If not, we should specify the value as FALSE.<\/li>\n<\/ul>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
How to use VLOOKUP function<\/h2>\n
Numbers in the first column<\/h3>\n
Let\u2019s try out on a few examples. Here is a test data set with sales figures for a group of salespeople. I will try to answer various business case scenarios (column D), using VLOOKUP. You\u2019ll observe how the index parameter value affects the outcome.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/VLOOKUP-1-1.png” alt=”vlookup-function-google-sheets-1″ title_text=”vlookup-function-google-sheets-1″ align=”center” _builder_version=”4.16″ _module_preset=”default” 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.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
I\u2019ll choose first example here to dissect and ensure that you understand the basic use of the function. The formula =VLOOKUP(11876,A2:C11,2,FALSE)<\/strong> is used to tell the function to search for the value 11,876 within the range of cells from A2 to C11. Once it finds the value, it is instructed to return the data in the second column of the row it found the data in. The False indicates that the data is not sorted, and that you want an exact match to the search key.<\/p>\n
Here\u2019s an interesting thing to note in the last case (row # 7 above). The function returned an #N\/A error. Why? That is, as explained in the error description, because the $15000 sales figure does not exist in the first row. Since I specified that I want an exact match (by indicating False for the is_sorted parameter), it cannot return an appropriate value.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
The curious case of [is_sorted]<\/h3>\n
In the examples, I have chosen FALSE for the last parameter, which tells the function to find an exact match with the search key. This is typically how the VLOOKUP function is used. But what happens when you use True instead to find an approximate match?<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/VLOOKUP-2-1.png” alt=”vlookup-function-google-sheets-2″ title_text=”vlookup-function-google-sheets-2″ align=”center” _builder_version=”4.16″ _module_preset=”default” 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.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Here, there are two examples that were use VLOOKUP to find the person with $12,000 of sales. The first function uses an is_sorted<\/strong> parameter of False, which tells the function to find an exact match. Because there is no salesperson with exactly $12,000 of sales, the function returns an error.<\/p>\n
The second example is identical except it uses True instead, which tells the function that the data is sorted and it should find an approximate match. The function then tries to find the sales number that is closest to $12,000, and returns the name of the corresponding salesperson \u2013 Finch.<\/p>\n
Notice that the salesperson closest to $12,000 sales is actually Gary. The VLOOKUP function finds an approximate match by finding the value that is closest but not more than the search key. It therefore returns Finch here, even though Gary is technically closer to the search key. This is an important feature to take note of, since it could impact your results when using this function!<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Use case: Sorted strings in the first column<\/h3>\n
Now that you\u2019re getting more familiar with the function, let\u2019s try an example data set in which the first column has sorted string values, as shown below. There are two examples each for all the three cases available, but with one distinction of is_sorted parameter. Please observe how the VLOOKUP function is behaving.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/VLOOKUP-3-1.png” alt=”vlookup-function-google-sheets-3″ title_text=”vlookup-function-google-sheets-3″ align=”center” _builder_version=”4.16″ _module_preset=”default” 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.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
You can see that the TRUE or FALSE value for is_sorted<\/strong> parameter doesn\u2019t really affect the behaviour when it finds the search_key<\/strong> within the first column of the range. But an interesting development happens when it doesn\u2019t find an exact match for the search_key<\/strong>. In the case of FALSE (row # 8), it was looking for an exact match. But in the case of TRUE (row # 9), it is looking for an approximate match, and instead of Greg, it returns the information for Gary. This behaviour may or may not be desirable depending on your situation!<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Unsorted strings in the first column<\/h3>\n
The data for this case is essentially the same as that used above, except there are non-sorted strings in the first column.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/VLOOKUP-4-1.png” alt=”vlookup-function-google-sheets-4″ title_text=”vlookup-function-google-sheets-4″ align=”center” _builder_version=”4.16″ _module_preset=”default” 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.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Unlike the previous example, the use of TRUE and FALSE values for the fourth parameter is generating different results. Because the data is not sorted, the function cannot give an accurate approximate match. When the data is not sorted, that the results are correct only when I used FALSE for is_sorted<\/strong>.<\/p>\n
So, what is the bottom line?<\/p>\n
Never lie to Google Sheets in the is_sorted<\/strong> parameter \u2014 if you do, it will return the favor!<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Multiple matches in the first column<\/h3>\n
There will be times when you may encounter multiple instances of the same value in the first column. In the example below, Barry occurs twice. What happens if you use the VLOOKUP function in such a scenario?<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/VLOOKUP-5-1.png” alt=”vlookup-function-google-sheets-5″ title_text=”vlookup-function-google-sheets-5″ align=”center” _builder_version=”4.16″ _module_preset=”default” 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.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
You may have noticed, it picks up the first Barry it encounters in the list. And the second Barry is not considered.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Drawbacks with the VLOOKUP function<\/h5>\n
There are two problems with this formula:<\/p>\n
- \n
- For looking up search_key<\/strong>, it always uses the first column within the input range<\/strong>. So it is not possible with VLOOKUP function to fetch a cell value that is to the left of the lookup column.<\/li>\n
- The function is not dynamic enough, in that, the column index values are not updated if we inserted a column between the input range<\/strong>.<\/li>\n<\/ol>\n
There\u2019s an alternative that solves the above two problems. Please go through the explanation for the INDEX and MATCH function combination<\/a>.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.4.8″ background_color=”#f9f9ff” custom_padding=”15px|25px|15px|25px|true|true” border_width_left=”3px” border_color_left=”#808e95″ global_module=”26540″ saved_tabs=”all” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Editor\u2019s note<\/strong>: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.<\/em><\/p>\n
\n[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":"
The VLOOKUP Google Sheets function is perhaps one of the most widely used functions in Google Sheets. It stands for Vertical Lookup. This searches for a key value in the first column of the input range, and it returns the value of a specified cell from the row where it finds the key. You can […]<\/p>\n","protected":false},"author":40,"featured_media":29589,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"Given its utility, VLOOKUP<\/a> formula is perhaps one of the most widely used formulas in Google Sheets. It stands for 'Vertical Lookup'. This searches for a key value in the first column of the input range. And, it returns the value of a specified cell from the row where it finds the key. We can expect an error if the key doesn't exist.\n
Syntax<\/h3>\nVLOOKUP(search_key, range, index, [is_sorted])<\/span><\/strong>\n
- \n \t
- search_key<\/strong><\/span> - is the value that the VLOOKUP formula uses to search.<\/li>\n \t
- range<\/strong><\/span> - is the reference to the range of cells that we are performing a search on. Google Sheets application looks for the key in the first column of the range.<\/li>\n \t
- index<\/strong><\/span> - this is the column index of the cell within the range, whose value the formula returns. The first column's index within the range<\/strong><\/span> is 1, the second in the range<\/strong><\/span> is 2 and so on. For example, if we input 3 against this parameter, the VLOOKUP formula returns the cell value from the third column and the row in which it finds the search_key<\/strong><\/span>.<\/li>\n \t
- [is_sorted]<\/strong><\/span> - is an optional parameter which is TRUE by default. This indicates whether the first column in the range<\/strong><\/span> is sorted in ascending order or not. If not, we should specify the value as FALSE.<\/li>\n<\/ul>\n
Usage: VLOOKUP Formula<\/h3>\n
Use case: Numbers in the first column<\/h5>\nLet us try our hands on a few examples. Here is a test data with sales figures generated by a set of salespersons. We tried to answer various business case scenarios (column D), using this formula. You'll observe how the index<\/strong><\/span> parameter value affects the outcome.\n\n
![\"VLOOKUP](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/Vlookup1.png\")
\n\nHere's an interesting thing to note in the last case (row # 7 above). The formula returned an #N\/A! error. Why? That is, as explained in the error description because the $15000 sales figure does not exist in the first column. Therefore, it cannot return an appropriate value.\n<\/h5>\n
The curious case of [is_sorted]<\/span><\/h5>\nIn the examples, we have chosen FALSE for the last parameter if the first column within the input range is not sorted in ascending order. We chose TRUE otherwise. Now, what if we tried to do the opposite?\n\n
![\"VLOOKUP](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/vlookup2.png\")
\n\nThe second case worked out well. But the first one, not as much! Why is that? Here is a note from the official documentation<\/a> of VLOOKUP formula:\nIf the is_sorted argument is set to TRUE or omitted, and the first column of the range is not in sorted order, an incorrect value might be returned.<\/blockquote>\nSo, there, they said it themselves! It will not give us expected results when we go with the TRUE option when the first column is not sorted. However, if we have sorted values in the first column, using TRUE for the last parameter will result in a much better performance.\n
<\/h5>\n
Use case: Sorted strings in the first column<\/h5>\nNow that we got our hands greased with numbers, let us try with an example data set, in which the first column has sorted string values, as shown below. There are two examples each for all the three cases available, but with one distinction of is_sorted<\/strong><\/span> parameter. Please observe how the VLOOKUP formula is behaving.\n\n
![\"VLOOKUP](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/vlookup3.png\")
\n\nApparently, the TRUE or FALSE value for is_sorted<\/strong><\/span> parameter doesn't really affect the behavior when it finds the search_key<\/span><\/strong> within the first column of the range. But an interesting development happens when it doesn't find a match for the search_key<\/strong><\/span>. In the case of FALSE (row # 8), it was looking for an exact match. But in the case of TRUE (row # 9), it apparently is looking for a near match. This, obviously, may not be desirable in many cases.\n<\/h5>\n
Use case: Unsorted strings in the first column<\/h5>\nThe data we are considering for this use case is essentially the same as that used above. Except, we have non-sorted strings in the first row.\n\n
![\"VLOOKUP](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/vlookup4.png\")
\n\nUnlike the previous example, the use of TRUE and FALSE values for the fourth parameter is generating different results. And we can deduce that the results are correct only when we used FALSE for is_sorted<\/strong><\/span>.\n\nSo, what is the bottom line?\nNever lie to Google Sheets in the is_sorted<\/span><\/strong> parameter! Seemingly, it returns the favor!<\/blockquote>\n
<\/h5>\n
Use case: Multiple matches in the first column<\/h5>\nThere will be instances where we may encounter multiple instances of the same value in the first column. In the example below, Barry occurred twice. What happens if we use VLOOKUP formula in such scenario? Let's find out.\n\n
![\"VLOOKUP](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/lookup5.png\")
\n\nYou may have noticed, it picks up the first Barry it encounters in the list. And the second Barry is not considered.\n<\/h5>\n
Use case: Drawbacks with the VLOOKUP formula<\/h5>\nThere are two problems with this formula:\n
- \n \t
- For looking up search_key<\/strong><\/span>, it always uses the first column within the input range<\/strong><\/span>. So it is not possible with VLOOKUP formula to fetch a cell value that is to the left of the lookup column.<\/li>\n \t
- The formula is not dynamic enough, in that, the column index values are not updated if we inserted a column between the input range<\/strong><\/span>.<\/li>\n<\/ol>\nThere's an alternative that solves the above two problems. Please go through the explanation for the INDEX and MATCH formula combination here<\/a>.\n\n\u00a0","_et_gb_content_width":"","footnotes":""},"categories":[54],"tags":[39,28],"class_list":["post-3414","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google-sheets-formulas","tag-connections-t","tag-spreadsheets"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/3414","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/users\/40"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/comments?post=3414"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/3414\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media\/29589"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media?parent=3414"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/categories?post=3414"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/tags?post=3414"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
- range<\/strong><\/span> - is the reference to the range of cells that we are performing a search on. Google Sheets application looks for the key in the first column of the range.<\/li>\n \t
- The function is not dynamic enough, in that, the column index values are not updated if we inserted a column between the input range<\/strong>.<\/li>\n<\/ol>\n
- range<\/strong> \u2013 is the reference to the range of cells that we are performing a search on. Google Sheets application looks for the key in the first column of the range.<\/li>\n