[et_pb_section fb_built=”1″ module_class=”sheetgo-post” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_row _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
A common task in Excel is searching your spreadsheet for certain values, and using a function to do this and return a corresponding value can be very useful. The\u00a0LOOKUP<\/a>\u00a0function in Excel is one way to do this. While the VLOOKUP and HLOOKUP functions are more powerful, the LOOKUP function is great for quick and simple lookups to keep your spreadsheet running smoothly.<\/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 [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The LOOKUP function has two forms: vector form and array form.<\/p>\n This is the form of the function that you\u2019ll typically use. You specify the lookup value and the search and results vectors, and the function will find the best match.>\/p>[\/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”]=LOOKUP(lookup_value, lookup_vector, [result_vector])[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/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 This form is less common but can be just as useful.<\/p>\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”]=LOOKUP(lookup_value, array)[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/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 Now that you\u2019ve seen the syntax, I\u2019ll show some examples of the Excel LOOKUP function in action.<\/p>\n I\u2019ll start with the vector form since this is the most common. Here I have some data from a department store, and I\u2019m using the function to find Frank\u2019s department.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/1.-LOOKUP-vector-form.png” alt=”lookup function excel vector form 1″ title_text=”1. LOOKUP vector form” 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 enter \u201cFrank\u201d as the search key, and tell the function to search in A2:A12. I also instruct the function to return the corresponding answer from B2:B12.<\/p>\n I do this with the formula =LOOKUP(“Frank”,A2:A12,B2:B12)<\/b>. <\/p>\n Note that the two vectors are the same length \u2013 if they aren\u2019t, you\u2019ll get an error!<\/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 When you use the LOOKUP Excel function, your data needs to be sorted in ascending order. The LOOKUP function provides an approximate match to your search value when there\u2019s no exact match.<\/p>\n To do this, it needs sorted data. In this example, I demonstrate what happens when the data is not sorted.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/2.-LOOKUP-unsorted.png” alt=”lookup function excel unsorted 2″ title_text=”2. LOOKUP unsorted” 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, I\u2019m searching for the person with sales closest to $8,000. By typing =LOOKUP(8000,A2:A12,C2:C12)<\/b> I can make the LOOKUP function do just that.<\/p>\n But you can see here that the answer is wrong. The reason for that is my data isn\u2019t sorted, so the function can\u2019t tell what the right answer is!<\/p>\n I can fix that problem by sorting the data by sales in ascending order. Now I get the correct answer!<\/p>\n Note that the LOOKUP function will always return the value lower than the search value when there\u2019s no exact match. Even if Isaiah had $8,001 in sales, it would still return Emily because she\u2019s the closest to $8,000 without surpassing it!\n<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/3.-LOOKUP-sorted.png” alt=”lookup function excel sorted 3″ title_text=”3. LOOKUP sorted” 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\u2019ve now seen how the LOOKUP function in Excel works in vector format. As I mentioned previously, that\u2019s the more common format.<\/p>\n But you also have the option of using array format. The difference here is you only need to specify a lookup_value and a single array.<\/p>\n In the example below, I find Greg\u2019s sales using the formula =LOOKUP(“Greg”,A2:C12)<\/b>.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/02\/4.-LOOKUP-array-form.png” alt=”lookup function excel array form 4″ title_text=”4. LOOKUP array form” 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 The benefit of the array format is you only have to specify one array, instead of two separate vectors.<\/p>\n The function will automatically search the first column (or row if it\u2019s a horizontal array) for the lookup_value and return the result from the last column (or row).<\/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 [\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":" A common task in Excel is searching your spreadsheet for certain values, and using a function to do this and return a corresponding value can be very useful. The\u00a0LOOKUP\u00a0function in Excel is one way to do this. While the VLOOKUP and HLOOKUP functions are more powerful, the LOOKUP function is great for quick and simple […]<\/p>\n","protected":false},"author":40,"featured_media":31691,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[65],"tags":[39,48,28],"class_list":["post-31648","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-functions","tag-connections-t","tag-excel","tag-spreadsheets"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/31648","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=31648"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/31648\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media\/31691"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media?parent=31648"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/categories?post=31648"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/tags?post=31648"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Syntax<\/h3>\n
Vector form<\/h4>\n
\n
Array form<\/h4>\n
\n
How to use the LOOKUP function in Excel<\/h3>\n
Unsorted data<\/h4>\n
Array format<\/h4>\n
Tips for using the Excel LOOKUP function<\/h3>\n
\n