{"id":171,"date":"2011-09-19T16:17:24","date_gmt":"2011-09-19T16:17:24","guid":{"rendered":"http:\/\/www.microsofttraining.net\/b\/exceltraining\/?p=171"},"modified":"2023-12-30T23:14:25","modified_gmt":"2023-12-30T23:14:25","slug":"excel-courses-retrieve-data-using-the-index-and-match-functions","status":"publish","type":"post","link":"https:\/\/www.stl-training.co.uk\/b\/excel-courses-retrieve-data-using-the-index-and-match-functions\/","title":{"rendered":"Excel Courses – Retrieve Data using the Index and Match Functions"},"content":{"rendered":"

 <\/p>\n

If you have an Excel Worksheet<\/strong> that contains Data relating to the hours of training for each Employee for Microsoft Excel Training London<\/strong>\u00a0on Monday, Tuesday and Wednesday, you may want to return the hours\u00a0trained for a particular employee on a particular day in another Excel Workbook<\/strong> or Excel Worksheet<\/strong>.<\/p>\n

The spreadsheet shown below\u00a0is the data set we will use:<\/p>\n

\"\"<\/a>
Data Table Excel Workbook<\/figcaption><\/figure>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

The Excel INDEX<\/strong> function can return an item from a specific position in a table of Excel data using Row and Column headings. The\u00a0MATCH<\/strong> Function can return the position of a value in a list. The INDEX<\/strong> and MATCH<\/strong> functions used together\u00a0provide a flexible and powerful\u00a0method for extracting data from a\u00a0table.<\/p>\n

We start with the INDEX<\/strong> function. The Index function has three arguments(Array,Row_Num,[Col_Num])<\/p>\n

The Array<\/strong> argument is the range of cells we are trying to retrieve the data from. The Row_Num<\/strong> argument is the Row you are retrieving from and Col_Num<\/strong> the column you are retrieving from. The value at the intersection point of the Row and Column arguments will be returned from the cell range from the Array<\/strong> argument. We use the MATCH<\/strong> function to assign the Row and Column to the second and third arguments of the INDEX<\/strong> function.<\/p>\n

The Match<\/strong> function has three arguments:<\/p>\n

(Lookup_Value,Lookup_Array,[Match_Type])<\/p>\n

Here is the spreadsheet we will use to create the formula which will return the number of hours. The formula is entered into cell B5 of the Index and Match formula sheet:<\/p>\n

\"\"<\/a>
Index and Match formula sheet<\/figcaption><\/figure>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

The screenshot below shows the result and notes:<\/p>\n

=INDEX(‘[Data Table Excel Workbook.xlsx]Sheet1′!$B$2:$D$5,MATCH(B2,'[Data Table Excel Workbook.xlsx]Sheet1′!$A$2:$A$5,FALSE),MATCH(B3,'[Data Table Excel Workbook.xlsx]Sheet1’!$B$1:$D$1,FALSE))<\/p>\n

Enter a value into B2 and a value into B3 of the Index and Match formula sheet\u00a0and in cell B5 of the Index and Match formula sheet it will return the hours of Excel Training completed for that Employee.<\/p>\n

Formula Explanation Notes:<\/p>\n

1. The first argument of the Index function relates to just the hourly figures in the Data Table Excel\u00a0workbook(B2:D5).<\/p>\n

2. The Row Argument of the Index function uses the Match function to match the value of cell B2 in the Index and Match Formula sheet to the corresponding value in the Data Table Excel\u00a0Workbook(A2:A5).<\/p>\n

3. The\u00a0Column Argument of the Index function uses the Match function to match the value of cell B3 in the Index and Match Formula sheet to the corresponding value in the Data Table Excel\u00a0Workbook(B1:D1).<\/p>\n

4.The formula returns the value form the Array argument that\u00a0represents the row and column values specified.<\/p>\n

5. The FALSE\u00a0statement returns the exact value found.\u00a0<\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

  If you have an Excel Worksheet that contains Data relating to the hours of training for each Employee for Microsoft Excel Training London\u00a0on Monday, Tuesday and Wednesday, you may want to return the hours\u00a0trained for a particular employee on a particular day in another Excel Workbook or Excel Worksheet. The spreadsheet shown below\u00a0is the […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[4],"tags":[98,202,219,297,313,332,351,353,461,470,492],"class_list":["post-171","post","type-post","status-publish","format-standard","hentry","category-excel-training","tag-columns","tag-excel-hints","tag-excel-tips","tag-how-to-retrieve-data","tag-index-function","tag-learn-excel","tag-match-function","tag-microsoft-excel","tag-retrieve-data","tag-rows","tag-sk"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/171","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/comments?post=171"}],"version-history":[{"count":1,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/171\/revisions"}],"predecessor-version":[{"id":6794,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/171\/revisions\/6794"}],"wp:attachment":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/media?parent=171"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/categories?post=171"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/tags?post=171"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}