{"id":249,"date":"2022-12-01T20:31:54","date_gmt":"2022-12-01T20:31:54","guid":{"rendered":"https:\/\/courses.lumenlearning.com\/suny-compappmgr\/chapter\/look-up-functions-and-formulas\/"},"modified":"2022-12-01T20:31:54","modified_gmt":"2022-12-01T20:31:54","slug":"look-up-functions-and-formulas","status":"publish","type":"chapter","link":"https:\/\/courses.lumenlearning.com\/suny-compappmgr\/chapter\/look-up-functions-and-formulas\/","title":{"raw":"Lookup Functions and Formulas","rendered":"Lookup Functions and Formulas"},"content":{"raw":"\n<div class=\"textbox learning-objectives\">\n<h3>Learning Outcomes<\/h3>\n<ul>\n \t<li>Use lookup functions and formulas<\/li>\n<\/ul>\n<\/div>\nLookup functions in Excel are used for looking through a single column or row to find a particular value from the same place in a second column or row. This often takes place when there are multiple worksheets within a workbook or a large amount of data in a worksheet. Lookup functions are very helpful with large sets of data to find specific information across many rows or columns.\n<h2>VLOOKUP (Vertical Lookup)<\/h2>\nLooks for a value in the leftmost column of a table and returns a value in the same row from the vertical column you define. The lookup value must always be in the leftmost column of the table.\n\nThe Excel formula is written <strong>=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])<\/strong>\n\nThis table includes the sales totals for salespeople. In this case you want to find out what the sales were for certain salespeople. This is a small example but imagine a long list of names and numbers to search through. Using the VLOOKUP function quickly brings you the results you want. Be sure to use F4 to create the absolute references and keep the table array the same for each lookup. Also make sure and make the col_index_num use the second column in the table (column 2 in the table) with a return for the range_lookup as FALSE. By using FALSE in all cases, there is no need to sort your tables into ascending order. Here is what those results look like.\n\n<img class=\"alignnone wp-image-3035 size-full\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/3008\/2019\/12\/20195213\/Module_8_Statistical_VLookup_1.png\" alt=\"Excel screenshot displaying the lookup data and formula for the VLOOKUP function of a sales table.\" width=\"553\" height=\"403\">&nbsp;<img class=\"alignnone wp-image-3034 size-full\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/3008\/2019\/12\/20195209\/Module_8_Statistical_VLookup_1.1.png\" alt=\"Excel screenshot displaying the lookup return for the VLOOKUP function of a sales table.\" width=\"485\" height=\"392\">\n\nFor another VLOOKUP example, watch this video of how to use this function to find parts that are in stock and the supplier for particular parts.\n\n<iframe src=\"\/\/plugin.3playmedia.com\/show?mf=1578411&amp;p3sdk_version=1.10.1&amp;p=20361&amp;pt=375&amp;video_id=26m5wuDb6lc&amp;video_target=tpm-plugin-gkyymi3s-26m5wuDb6lc\" width=\"800px\" height=\"450px\" frameborder=\"0\" marginwidth=\"0px\" marginheight=\"0px\"><\/iframe>\n<h2>HLOOKUP (Horizontal Lookup)<\/h2>\nLooks for a value in the topmost row of a table and returns a value in the same row from the horizontal column you define. The lookup value must always be in the leftmost column of the table.\n\nThe Excel formula is written <strong>=HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])<\/strong>\n\n<iframe src=\"\/\/plugin.3playmedia.com\/show?mf=4356781&amp;p3sdk_version=1.10.1&amp;p=20361&amp;pt=375&amp;video_id=1BgAsfcFAuI&amp;video_target=tpm-plugin-jot5eiw8-1BgAsfcFAuI\" width=\"800px\" height=\"450px\" frameborder=\"0\" marginwidth=\"0px\" marginheight=\"0px\"><\/iframe>\n<h2>MATCH and INDEX Functions<\/h2>\nNow we\u2019ll learn two additional lookup functions, Match and Index. Watch this video to learn how to use Match and Index lookup functions. Here are the descriptions and formulas for creating them in Excel.\n\n<iframe src=\"\/\/plugin.3playmedia.com\/show?mf=4356782&amp;p3sdk_version=1.10.1&amp;p=20361&amp;pt=375&amp;video_id=-dKjC2KKPzs&amp;video_target=tpm-plugin-h5voa54b--dKjC2KKPzs\" width=\"800px\" height=\"450px\" frameborder=\"0\" marginwidth=\"0px\" marginheight=\"0px\"><\/iframe>\n<ul>\n \t<li style=\"font-weight: 400;\">MATCH \u2013 Function that returns a position of a value in a range which can be a row, column or table. The Excel formula is written&nbsp;<strong>=MATCH(lookup_value,lookup_array,[match_type])<\/strong><\/li>\n \t<li style=\"font-weight: 400;\">INDEX \u2013 Function that returns a position of a value in a range which looks at the intersection of a column and row position in a table. The Excel formula is written <strong>=INDEX(reference,row_num,[column_num],[area_num])<\/strong><\/li>\n<\/ul>\nAs you become more familiar with lookup functions, try out a few more by selecting the Formulas tab, Lookup &amp; Reference button and see what others are available for you to try.\n\n<img class=\"alignnone wp-image-3036 size-full\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/3008\/2019\/12\/20195352\/Module_8_Statistical_Lookup_VLookup.png\" alt=\"Excel table open with &quot;Lookup &amp; Reference&quot; button selected, revealing a dropdown menu of 21 items. VLOOKUP is selected: \" width=\"1225\" height=\"797\">\n<div class=\"textbox tryit\">\n<h3>PRactice Questions<\/h3>\nhttps:\/\/assess.lumenlearning.com\/practice\/4083ea1d-2fbc-4d80-b503-5fd58212713a\n\nhttps:\/\/assess.lumenlearning.com\/practice\/ea286f75-21dc-4f79-9ada-343dd6dae407\n\n<\/div>\n<h2>Contribute!<\/h2><div style=\"margin-bottom: 8px;\">Did you have an idea for improving this content? We\u2019d love your input.<\/div><a href=\"https:\/\/docs.google.com\/document\/d\/1AqOjStGxvB-GjYIOZBvHx2FZbKEJK0TD-imjhCyGBQ0\" target=\"_blank\" style=\"font-size: 10pt; font-weight: 600; color: #077fab; text-decoration: none; border: 2px solid #077fab; border-radius: 7px; padding: 5px 25px; text-align: center; cursor: pointer; line-height: 1.5em;\">Improve this page<\/a><a style=\"margin-left: 16px;\" target=\"_blank\" href=\"https:\/\/docs.google.com\/document\/d\/1vy-T6DtTF-BbMfpVEI7VP_R7w2A4anzYZLXR8Pk4Fu4\">Learn More<\/a>\n","rendered":"<div class=\"textbox learning-objectives\">\n<h3>Learning Outcomes<\/h3>\n<ul>\n<li>Use lookup functions and formulas<\/li>\n<\/ul>\n<\/div>\n<p>Lookup functions in Excel are used for looking through a single column or row to find a particular value from the same place in a second column or row. This often takes place when there are multiple worksheets within a workbook or a large amount of data in a worksheet. Lookup functions are very helpful with large sets of data to find specific information across many rows or columns.<\/p>\n<h2>VLOOKUP (Vertical Lookup)<\/h2>\n<p>Looks for a value in the leftmost column of a table and returns a value in the same row from the vertical column you define. The lookup value must always be in the leftmost column of the table.<\/p>\n<p>The Excel formula is written <strong>=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])<\/strong><\/p>\n<p>This table includes the sales totals for salespeople. In this case you want to find out what the sales were for certain salespeople. This is a small example but imagine a long list of names and numbers to search through. Using the VLOOKUP function quickly brings you the results you want. Be sure to use F4 to create the absolute references and keep the table array the same for each lookup. Also make sure and make the col_index_num use the second column in the table (column 2 in the table) with a return for the range_lookup as FALSE. By using FALSE in all cases, there is no need to sort your tables into ascending order. Here is what those results look like.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-3035 size-full\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/3008\/2019\/12\/20195213\/Module_8_Statistical_VLookup_1.png\" alt=\"Excel screenshot displaying the lookup data and formula for the VLOOKUP function of a sales table.\" width=\"553\" height=\"403\" \/>&nbsp;<img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-3034 size-full\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/3008\/2019\/12\/20195209\/Module_8_Statistical_VLookup_1.1.png\" alt=\"Excel screenshot displaying the lookup return for the VLOOKUP function of a sales table.\" width=\"485\" height=\"392\" \/><\/p>\n<p>For another VLOOKUP example, watch this video of how to use this function to find parts that are in stock and the supplier for particular parts.<\/p>\n<p><iframe loading=\"lazy\" src=\"\/\/plugin.3playmedia.com\/show?mf=1578411&amp;p3sdk_version=1.10.1&amp;p=20361&amp;pt=375&amp;video_id=26m5wuDb6lc&amp;video_target=tpm-plugin-gkyymi3s-26m5wuDb6lc\" width=\"800px\" height=\"450px\" frameborder=\"0\" marginwidth=\"0px\" marginheight=\"0px\"><\/iframe><\/p>\n<h2>HLOOKUP (Horizontal Lookup)<\/h2>\n<p>Looks for a value in the topmost row of a table and returns a value in the same row from the horizontal column you define. The lookup value must always be in the leftmost column of the table.<\/p>\n<p>The Excel formula is written <strong>=HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])<\/strong><\/p>\n<p><iframe loading=\"lazy\" src=\"\/\/plugin.3playmedia.com\/show?mf=4356781&amp;p3sdk_version=1.10.1&amp;p=20361&amp;pt=375&amp;video_id=1BgAsfcFAuI&amp;video_target=tpm-plugin-jot5eiw8-1BgAsfcFAuI\" width=\"800px\" height=\"450px\" frameborder=\"0\" marginwidth=\"0px\" marginheight=\"0px\"><\/iframe><\/p>\n<h2>MATCH and INDEX Functions<\/h2>\n<p>Now we\u2019ll learn two additional lookup functions, Match and Index. Watch this video to learn how to use Match and Index lookup functions. Here are the descriptions and formulas for creating them in Excel.<\/p>\n<p><iframe loading=\"lazy\" src=\"\/\/plugin.3playmedia.com\/show?mf=4356782&amp;p3sdk_version=1.10.1&amp;p=20361&amp;pt=375&amp;video_id=-dKjC2KKPzs&amp;video_target=tpm-plugin-h5voa54b--dKjC2KKPzs\" width=\"800px\" height=\"450px\" frameborder=\"0\" marginwidth=\"0px\" marginheight=\"0px\"><\/iframe><\/p>\n<ul>\n<li style=\"font-weight: 400;\">MATCH \u2013 Function that returns a position of a value in a range which can be a row, column or table. The Excel formula is written&nbsp;<strong>=MATCH(lookup_value,lookup_array,[match_type])<\/strong><\/li>\n<li style=\"font-weight: 400;\">INDEX \u2013 Function that returns a position of a value in a range which looks at the intersection of a column and row position in a table. The Excel formula is written <strong>=INDEX(reference,row_num,[column_num],[area_num])<\/strong><\/li>\n<\/ul>\n<p>As you become more familiar with lookup functions, try out a few more by selecting the Formulas tab, Lookup &amp; Reference button and see what others are available for you to try.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-3036 size-full\" src=\"https:\/\/s3-us-west-2.amazonaws.com\/courses-images\/wp-content\/uploads\/sites\/3008\/2019\/12\/20195352\/Module_8_Statistical_Lookup_VLookup.png\" alt=\"Excel table open with &quot;Lookup &amp; Reference&quot; button selected, revealing a dropdown menu of 21 items. VLOOKUP is selected:\" width=\"1225\" height=\"797\" \/><\/p>\n<div class=\"textbox tryit\">\n<h3>PRactice Questions<\/h3>\n<p>\t<iframe id=\"assessment_practice_4083ea1d-2fbc-4d80-b503-5fd58212713a\" class=\"resizable\" src=\"https:\/\/assess.lumenlearning.com\/practice\/4083ea1d-2fbc-4d80-b503-5fd58212713a?iframe_resize_id=assessment_practice_id_4083ea1d-2fbc-4d80-b503-5fd58212713a\" frameborder=\"0\" style=\"border:none;width:100%;height:100%;min-height:300px;\"><br \/>\n\t<\/iframe><\/p>\n<p>\t<iframe id=\"assessment_practice_ea286f75-21dc-4f79-9ada-343dd6dae407\" class=\"resizable\" src=\"https:\/\/assess.lumenlearning.com\/practice\/ea286f75-21dc-4f79-9ada-343dd6dae407?iframe_resize_id=assessment_practice_id_ea286f75-21dc-4f79-9ada-343dd6dae407\" frameborder=\"0\" style=\"border:none;width:100%;height:100%;min-height:300px;\"><br \/>\n\t<\/iframe><\/p>\n<\/div>\n<h2>Contribute!<\/h2>\n<div style=\"margin-bottom: 8px;\">Did you have an idea for improving this content? We\u2019d love your input.<\/div>\n<p><a href=\"https:\/\/docs.google.com\/document\/d\/1AqOjStGxvB-GjYIOZBvHx2FZbKEJK0TD-imjhCyGBQ0\" target=\"_blank\" style=\"font-size: 10pt; font-weight: 600; color: #077fab; text-decoration: none; border: 2px solid #077fab; border-radius: 7px; padding: 5px 25px; text-align: center; cursor: pointer; line-height: 1.5em;\">Improve this page<\/a><a style=\"margin-left: 16px;\" target=\"_blank\" href=\"https:\/\/docs.google.com\/document\/d\/1vy-T6DtTF-BbMfpVEI7VP_R7w2A4anzYZLXR8Pk4Fu4\">Learn More<\/a><\/p>\n\n\t\t\t <section class=\"citations-section\" role=\"contentinfo\">\n\t\t\t <h3>Candela Citations<\/h3>\n\t\t\t\t\t <div>\n\t\t\t\t\t\t <div id=\"citation-list-249\">\n\t\t\t\t\t\t\t <div class=\"licensing\"><div class=\"license-attribution-dropdown-subheading\">CC licensed content, Original<\/div><ul class=\"citation-list\"><li>Look-up Functions and Formulas. <strong>Authored by<\/strong>: Sherri Pendleton. <strong>Provided by<\/strong>: Lumen Learning. <strong>License<\/strong>: <em><a target=\"_blank\" rel=\"license\" href=\"https:\/\/creativecommons.org\/licenses\/by\/4.0\/\">CC BY: Attribution<\/a><\/em><\/li><\/ul><\/div>\n\t\t\t\t\t\t <\/div>\n\t\t\t\t\t <\/div>\n\t\t\t <\/section>","protected":false},"author":503070,"menu_order":14,"template":"","meta":{"_candela_citation":"[{\"type\":\"original\",\"description\":\"Look-up Functions and Formulas\",\"author\":\"Sherri Pendleton\",\"organization\":\"Lumen Learning\",\"url\":\"\",\"project\":\"\",\"license\":\"cc-by\",\"license_terms\":\"\"}]","CANDELA_OUTCOMES_GUID":"251ce209-5e67-4bc1-b0c6-9e88b645c93b, be0be606-a1b5-4f79-a1ed-a95bb12673b6","pb_show_title":"on","pb_short_title":"","pb_subtitle":"","pb_authors":[],"pb_section_license":""},"chapter-type":[],"contributor":[],"license":[],"class_list":["post-249","chapter","type-chapter","status-publish","hentry"],"part":235,"_links":{"self":[{"href":"https:\/\/courses.lumenlearning.com\/suny-compappmgr\/wp-json\/pressbooks\/v2\/chapters\/249","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/courses.lumenlearning.com\/suny-compappmgr\/wp-json\/pressbooks\/v2\/chapters"}],"about":[{"href":"https:\/\/courses.lumenlearning.com\/suny-compappmgr\/wp-json\/wp\/v2\/types\/chapter"}],"author":[{"embeddable":true,"href":"https:\/\/courses.lumenlearning.com\/suny-compappmgr\/wp-json\/wp\/v2\/users\/503070"}],"version-history":[{"count":0,"href":"https:\/\/courses.lumenlearning.com\/suny-compappmgr\/wp-json\/pressbooks\/v2\/chapters\/249\/revisions"}],"part":[{"href":"https:\/\/courses.lumenlearning.com\/suny-compappmgr\/wp-json\/pressbooks\/v2\/parts\/235"}],"metadata":[{"href":"https:\/\/courses.lumenlearning.com\/suny-compappmgr\/wp-json\/pressbooks\/v2\/chapters\/249\/metadata\/"}],"wp:attachment":[{"href":"https:\/\/courses.lumenlearning.com\/suny-compappmgr\/wp-json\/wp\/v2\/media?parent=249"}],"wp:term":[{"taxonomy":"chapter-type","embeddable":true,"href":"https:\/\/courses.lumenlearning.com\/suny-compappmgr\/wp-json\/pressbooks\/v2\/chapter-type?post=249"},{"taxonomy":"contributor","embeddable":true,"href":"https:\/\/courses.lumenlearning.com\/suny-compappmgr\/wp-json\/wp\/v2\/contributor?post=249"},{"taxonomy":"license","embeddable":true,"href":"https:\/\/courses.lumenlearning.com\/suny-compappmgr\/wp-json\/wp\/v2\/license?post=249"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}