{"id":30301,"date":"2023-08-07T19:40:38","date_gmt":"2023-08-07T17:40:38","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=30301"},"modified":"2024-06-06T20:49:50","modified_gmt":"2024-06-06T18:49:50","slug":"index-in-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/index-in-excel\/","title":{"rendered":"Master the Power of INDEX in Excel: 2 Different Forms – Array and Reference"},"content":{"rendered":"

\"Master<\/a><\/p>\n

If you find yourself working with Excel on a daily basis, you’ve likely come across various functions to make your tasks efficient and streamlined<\/strong>. One such powerful function is the INDEX<\/strong> Function. It is used to extract the value of a cell in a table based on the specified column and row number<\/strong>.<\/p>\n

In this article, you will explore the following topics –<\/p>\n

Download the Excel Workbook below to follow along and understand how to use INDEX in Excel \u2013 download excel workbook<\/strong><\/span>INDEX-in-Excel.xlsx<\/span><\/a><\/p>\n

 <\/p>\n

Introduction to Index in Excel<\/span><\/strong><\/h3>\n

The INDEX function is one of the most popular tools<\/strong> used in Excel and<\/a> is a perfect upgrade to the VLOOKUP function. It retrieves\u00a0data from a specific row and column<\/strong> within an array. It offers the flexibility to fetch individual values, complete rows, or entire columns<\/strong>.<\/p>\n

You can easily use the INDEX function When you know the row and number from which the data needs to be retrieved. For instance, if you have a sales table, and you wish to find the sales figure<\/strong> for Camera<\/strong> in Quarter 3<\/strong>, you can effortlessly achieve this with the INDEX Function.<\/p>\n

No manual data search is required!<\/p>\n

\"Master<\/a><\/p>\n

There are 2 forms<\/strong> in the INDEX function – array form<\/strong> and reference form<\/strong>.<\/p>\n

Index in Array form<\/strong> allows input of only one range<\/strong> or array. Whereas, Index in Reference<\/strong> form will enable you to use multiple arrays<\/strong> along with an optional argument to specify the desired array.<\/p>\n

Let’s explore the two different forms with examples.<\/p>\n

 <\/p>\n

#1 – INDEX in Array Form<\/span><\/strong><\/h3>\n

Index in Array Form is a commonly used<\/strong> and more straightforward<\/strong> version. It allows you to extract values from a single table or array<\/strong> based on the row and column number specified by the user. The syntax<\/strong> is as follows –<\/p>\n

=INDEX(array, row_num, [column_num])<\/strong><\/p>\n