{"id":561,"date":"2019-04-17T13:12:46","date_gmt":"2019-04-17T10:12:46","guid":{"rendered":"https:\/\/www.test4u.eu\/excelpedia\/?p=561"},"modified":"2019-05-06T12:53:56","modified_gmt":"2019-05-06T09:53:56","slug":"how-to-use-the-index-function","status":"publish","type":"post","link":"https:\/\/www.test4u.eu\/excelpedia\/how-to-use-the-index-function\/","title":{"rendered":"How to use the INDEX function"},"content":{"rendered":"\n<p>The <strong><a href=\"https:\/\/www.test4u.eu\/excelpedia\/the-index-function\/\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"INDEX  (opens in a new tab)\">INDEX<\/a><\/strong><a href=\"https:\/\/www.test4u.eu\/excelpedia\/the-index-function\/\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"INDEX  (opens in a new tab)\"> <\/a>function is one of the most interesting functions in excel. It can be combined with many other functions to help you out of some difficult situations.<\/p>\n\n\n\n<figure class=\"wp-block-embed-youtube wp-block-embed is-type-video is-provider-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<span class=\"embed-youtube\" style=\"text-align:center; display: block;\"><iframe class=\"youtube-player\" width=\"640\" height=\"360\" src=\"https:\/\/www.youtube.com\/embed\/5YuPYfDfejI?version=3&#038;rel=0&#038;showsearch=0&#038;showinfo=1&#038;iv_load_policy=1&#038;fs=1&#038;hl=en-US&#038;autohide=2&#038;wmode=transparent\" allowfullscreen=\"true\" style=\"border:0;\" sandbox=\"allow-scripts allow-same-origin allow-popups allow-presentation allow-popups-to-escape-sandbox\"><\/iframe><\/span>\n<\/div><\/figure>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"has-large-font-size\"><strong>Practice File:<\/strong><\/p>\n\n\n\n<div class=\"wp-block-file\"><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/index.xlsx\">index<\/a><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/index.xlsx\" class=\"wp-block-file__button\" download>Download<\/a><\/div>\n\n\n\n<div style=\"height:100px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"has-medium-font-size\"><strong>Transcript<\/strong><\/p>\n\n\n\n<p>Let\u2019s start with the\nbasics.<\/p>\n\n\n\n<p>It comes in two\nformats. An array format and a range format.<\/p>\n\n\n\n<p>The array format looks\nup a reference to a cell within a single, cell range.<\/p>\n\n\n\n<p>Suppose we want, in\nour example, to calculate in cell c9 the grade of student 2 (Mary) to lesson 3\n(chemistry).<\/p>\n\n\n\n<p>We type the name of\nthe function and open the parenthesis. Notice the two different formats of the\nfunction.<\/p>\n\n\n\n<p>We select the cell\nrange that shows the grades and then the row (number 2) and the column (number\n3).<\/p>\n\n\n\n<p>As expected we have\nthe grade of Mary in Chemistry which is 6.<\/p>\n\n\n\n<p>If one of the row or column numbers are set to zero, then <strong>INDEX<\/strong> returns the whole column or row respectively.<\/p>\n\n\n\n<p>Suppose that we want to calculate the average grade of Mary using INDEX. We alter our formula by setting the column number to 0, thus making INDEX to return to us the whole second row of data. We enclose this in an Average function and there we are. The average grade of Mary.<\/p>\n\n\n\n<p>We should note here\nthat we can\u2019t set both row and column number to zero, because it would result\nin an error.<\/p>\n\n\n\n<p>It looks rather easy\ntill now, and not that interesting.<\/p>\n\n\n\n<p>Let\u2019s see now the\nrange format of the function.<\/p>\n\n\n\n<p>In our next sheet we\ncan see the grades of the students for all trimesters.<\/p>\n\n\n\n<p>Using the range format, we can add to our INDEX function the cell ranges with the grades for each trimester.<\/p>\n\n\n\n<p>We will calculate the average grade of Mary for the first trimester in cell C17.<\/p>\n\n\n\n<p>We type the average\nfunction and inside it, we will enclose an index function. For the first\nattribute of the index function we have to provide the range. Our range\noriginates from three different cell ranges. So we open a parenthesis and\nprovide the three different cell areas divided by commas, and then close the\nparenthesis.<\/p>\n\n\n\n<p>Then we type the row\nnumber which is 2 for Mary. The column number is 0 since we want the whole row,\nand then as we can see from the auto complete we are required to enter an area\nnumber. The area number refers to the cell areas we entered before and is\nrelative to the order we entered them. So for the first trimester we enter 1.\nThe area number is optional and if skipped it defaults to 1.<\/p>\n\n\n\n<p>We close the\nparenthesis and we have the desired result as on the previous sheet.<\/p>\n\n\n\n<p>If we want to see the\naverage of another trimester we just change the area number to its respective\none. So for the second trimester we enter 2 and for the third trimester we\nenter 3.<\/p>\n\n\n\n<p>I told you at the\nbeginning that index is one of the most interesting functions in excel, and I\u2019m\nsure that you might feel a bit disappointed.<\/p>\n\n\n\n<p>Bear with me for a few more lessons and you will find out that <a rel=\"noreferrer noopener\" aria-label=\"index combined with match make up the best lookup formulas (opens in a new tab)\" href=\"https:\/\/www.test4u.eu\/excelpedia\/advanced-lookup-functions-combinations-part-3\/\" target=\"_blank\"><strong>index combined with match make up the best lookup formulas<\/strong><\/a> there are and probably the ones you are going to use every time. I know I do.<\/p>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n","protected":false},"excerpt":{"rendered":"<p>The INDEX function is one of the most interesting functions in excel. It can be combined with many other functions to help you out of some difficult situations.<\/p>\n","protected":false},"author":2,"featured_media":562,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[3],"tags":[50,49],"class_list":["post-561","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials","tag-index","tag-lookup-functions"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/index.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/paRnIz-93","_links":{"self":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/561","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/comments?post=561"}],"version-history":[{"count":0,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/561\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media\/562"}],"wp:attachment":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media?parent=561"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/categories?post=561"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/tags?post=561"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}