range_lookup<\/b> is an optional parameter that can be either TRUE or FALSE. TRUE will provide an approximate match to the lookup_value while FALSE will mandate an exact match. The default is TRUE, but you\u2019ll typically want to specify FALSE. I\u2019ll talk more about this later<\/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 the VLOOKUP Excel function<\/h3>\n
Alright, now that the technical stuff is out of the way, I\u2019ll show you how to put this function to use. In the first example I\u2019ll show the function in its most basic form. I have a list of data from a department store, and I want to input the employee in cell E2 and have the spreadsheet automatically tell me their sales in cell F2. I do that with the formula =VLOOKUP(E2,A2:C11,3,FALSE)<\/b>.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/1.-VLOOKUP-basic.png” alt=”vlookup excel 1″ title_text=”1. VLOOKUP basic” align=”center” _builder_version=”4.23.1″ _module_preset=”default” 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 break this example down so you have a full understanding of it before moving on. My lookup_value here is a cell reference to cell E2, where I\u2019ve typed the name \u201cDavid\u201d. I can type any of the employees\u2019 names here and the formula will automatically adjust and tell me their sales number.<\/p>\n
Next, I\u2019ve selected the table_array of A2:C11. This array encompasses all my data. I then type 3 as my col_index_num which tells the function to return the answer from the third column of the array. With a quick look at the array you can see that the third column contains the sales information that I\u2019m looking for.<\/p>\n
Finally, I enter FALSE as my range_lookup because I want the function to find an exact match with my lookup_value. I\u2019ll show the alternative to this in the next example.<\/p>\n
So to recap what the function is doing: it\u2019s taking my lookup_value (David) and searching for David in the first column of the table_array. When it finds David in row 5, it takes the value from the cell in row 5 and the third column in the array (which I instructed it to do when I typed 3).<\/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
Approximate match<\/h4>\n
That first example dealt with an exact match. But what happens when I specify TRUE as the range_lookup? I\u2019ll demonstrate that here. For an approximate match, the function assumes that the data is sorted. It makes that assumption so that if it doesn\u2019t find an exact match to the lookup_value, it can estimate what an approximate match would be.<\/p>\n
In this example, I leave my data unsorted, which means that the approximate match won\u2019t work properly. I\u2019m doing this in order to demonstrate how it works, and then I\u2019ll show how to use it properly.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/2.-VLOOKUP-approximate.png” alt=”vlookup excel 2″ title_text=”2. VLOOKUP approximate” align=”center” _builder_version=”4.16″ _module_preset=”default” 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
The formula I use here is =VLOOKUP(E2,A2:C12,3,TRUE)<\/b>. Note that it\u2019s the exact same as in the previous example, except I typed TRUE instead of FALSE. Now what\u2019s happening here? Remember, the function assumes that the data is sorted, so when it looks for Daniel it stops at David because in a sorted dataset, Daniel should be before David. Because of this, it returns the value prior to David (Emily\u2019s sales) because that\u2019s where Daniel should be.<\/p>\n
In a sorted dataset, this might work alright. But when the data is not sorted, you can see that the answer here is completely wrong!<\/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
Approximate match (with sorted data)<\/h4>\n
OK, now that you understand how the function finds an approximate match, I\u2019ll show how to do it correctly \u2013 with sorted data. In this example, I\u2019m sorting the data by sales.<\/p>\n
Note that I had to rearrange the columns because VLOOKUP can only search in the leftmost column.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/3.-VLOOKUP-approximate-sorted.png” alt=”vlookup excel sorted 3″ title_text=”3. VLOOKUP approximate sorted” align=”center” _builder_version=”4.16″ _module_preset=”default” 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
The formula used here is =VLOOKUP(E2,A2:C12,2,TRUE)<\/b>. Even though nobody had exactly $15,000 in sales, the VLOOKUP Excel function correctly finds the closest value, and tells us who had it (Betty with $14,149).<\/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 lookup tables<\/h4>\n
You\u2019ve now seen a few basic examples of the VLOOKUP Excel function in action. For the majority of Excel users, that\u2019s all you really need to know.<\/p>\n
But if you want to see what else the function can do, take a look at this example with multiple lookup tables.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/4.-VLOOKUP-multiple-tables.png” alt=”vlookup excel mulitple tables 4″ title_text=”4. VLOOKUP multiple tables” align=”center” _builder_version=”4.16″ _module_preset=”default” 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
In this example, the store pays out a bonus to employees based on their sales. However, the clothing department has a different bonus structure than the other employees. How can you use VLOOKUP to automatically give the correct bonus to each employee? I\u2019ve done it here by typing =VLOOKUP(C2,IF(B2=”Clothing”,$F$3:$G$5,$F$9:$G$12),2,TRUE)<\/b>.<\/p>\n
The trick here is that I have not one but two different tables that I want the function to search, depending on the situation. So I include an IF statement within the table_array<\/b> parameter. The IF statement instructs the function to look in the Clothing table for employees in the clothing dept. and in the other bonus table for the other employees.<\/p>\n
I added dollar signs ($) in the table_array<\/b> because they tell Excel to hold those cells constant \u2013 so it doesn\u2019t change the arrays when I drag the formula down from the first cell.<\/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
Two-way lookup<\/h4>\n
Now I\u2019ll show you how to use VLOOKUP in a two-way lookup. In this example, I have sales numbers for both 2019 and 2020 for each employee.<\/p>\n
I can use the MATCH function to get the numbers from the year I want, with the formula =VLOOKUP(G3,A2:D12,MATCH(G2,A1:D1,0),FALSE)<\/b>.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/5.-VLOOKUP-two-way-lookup.png” alt=”vlookup excel two way lookup 5″ title_text=”5. VLOOKUP two way lookup” align=”center” _builder_version=”4.16″ _module_preset=”default” 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 put the MATCH function in the col_index_num<\/b> parameter because I want the column that I get the results from to be variable. <\/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