INDEX(reference, row_num, [column_num], [area_num]). error. This article explains how to use the INDEX and MATCH functions together in all versions of Excel, including Excel 2019 and Microsoft 365. Areas mentioned in INDEX Formula excel must be located on one sheet. The Index Function returns #VALUE! In the above example, the Index function has only one single range, and it returns a position to row 5 of the range C3:C7, which is cell C7. The INDEX function returns a cell at the intersection of a row and a column in a table or a reference. Then, if you copy the formula to another cell, it will contâ¦ The Excel INDEX function returns the value at a given position in a range or array. In this post I want to expose some lesser known quirks and ways it can be used. Returns the value of an element in a table or an array, selected by the row and column number indexes. Excel's INDEX function is a powerful function when you want to find a value in a specific row and column location inside a table of data. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. Get all the features you know and love in Windows 10. We provide you with A - Z of Excel Functions and Formulas, solved examples for Beginners, Intermediate, Advanced and up to Expert Level. The first row in the table is row 1 and the first column in the table is column 1. For example, if Reference describes the cells (A1:B4,D1:E4,G1:H4), area_num 1 is the range A1:B4, area_num 2 is the range D1:E4, and area_num 3 is the range G1:H4. This has the value of 629. [column_number] ) array: The range or a range name containing the table of data. The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function. A range of cells or an array constant. The INDEX function returns a value or the reference to a value from within a table or range. Combination of the two functions will give you a dynamic lookup approach: INDEX & MATCH: A Better Way to Look Up Data; If the array argument contains only a single row or column, you can omit the corresponding row_num or column_num argument. There are two ways to use the INDEX function: If you want to return the value of a specified cell or array of cells, see Array form. If you want to return a reference to specified cells, see Reference form. By default, the VLOOKUP function performs a case-insensitive lookup. As you can see from above image, INDEX MATCH Function has scanned 2 data columns to find the Price of Chromebooks. This nested function is more flexible than VLOOKUP and can yield results faster. Once you press the Enter Key, the INDEX MATCH Function will perform a Matrix search (Horizontally & Vertically) to bring the Price of Chromebook in Cell B13. These are described separately below. The INDEX function returns a cell value from a range, given a row and/or column position number. The array contains 1 and 2 in the first row and 3 and 4 in the second row. For more information on array formulas, see Guidelines and examples of array formulas. row_numÂ Â Â Â Required. Type an equal sign, the INDEX function name, and an opening parenthesis: =INDEX( 3. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.Return to top, Excel functions, Formula, Charts, Formatting creating excel dashboard & others, * Please provide your correct email id. The array form of the INDEX function has the following arguments: arrayÂ Â Â Â Required. column_number: column number in the array to be used to return the value.. INDEX Function in Excel â Examples â Returning a value. Returns the reference of the cell at the intersection of a particular row and column. row_num: It denotes the position of the row in the specified array. Result:In the above example, Index will return the cell reference to column number 4 and row number 3 of the range B3:F7, which is cell E5. Error if the area which is mentioned in the INDEX Formula in excel is in any other sheet. For example, what is the 3rd item in the 2nd column in the list below? INDEX is commonly used in combination with the MATCH function for powerful and version-independent lookup operations. No matter whether you are using Excel or Google Sheets, you can use the formula same way. 2. Below is the syntax of the INDEX function: =INDEX (array, row_num, [col_num]) =INDEX (array, row_num, [col_num], [area_num]) array â a range of cells or an array constant. error value. These examples use the INDEX function to find the value in the intersecting cell where a row and a column meet. =MATCH () returns the position of a cell in a row or column. If you set row_num or column_num to 0 (zero), INDEX returns the array of values for the entire column or row, respectively. Array: Array is defined as the specific range of cells / Range. You can use INDEX function in two ways â The function returns the value of an element in a table or an array, selected by the row and column number indexes. The number of the row in reference from which to return a reference. =INDEX () returns the value of a cell in a table based on the column and row number. How to use INDEX function in Excel. If the reference/array is a single row, you can put the col_num in the row_num argumentâs position; INDEX can return a â¦ The INDEX Function Returns a cell value from a list or table based on itâs column and row numbers. Our examples used numeric values to identify the row and column values that INDEX should work with. To look up a value of cell where an intersection of the row and column happens. The Excel INDEX function returns the value of an element in a range or array at a given position, specified by the row and column number indexes. Selects a range in reference from which to return the intersection of row_num and column_num. The Excel INDEX function can be combined with the MATCH. You may also look at these useful functions in excel –, Copyright © 2021. The Excel INDEX function returns the value at a given position in a range or array. The fact that you can use it with other functions (hint: MATCH) that can find the row number and the column number makes INDEX an extremely powerful Excel function. There are two ways to use the INDEX function in excel: This has the value of 629The example will return #VALUE if both row no, column no is zero. The INDEX function can be used alone, but nesting the MATCH function inside it creates an advanced lookup. The result of the INDEX function is a reference and is interpreted as such by other formulas. For example, the formula CELL("width",INDEX(A1:B2,1,2)) is equivalent to CELL("width",B1). Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. =INDEX(reference, row_num, [column_num], [area_num]), The reference format of Index is used only when a reference to a cell is within a multiple ranges, Note: If the Area_num is left blank then, the INDEX Function on Excel uses area 1 as default. The reference form of the INDEX function has the following arguments: referenceÂ Â Â Â Required. You can use INDEX to retrieve individual values or entire rows and columns. The Index function can also be used as a replacement for VLOOKUP when the data you want to retrieve is to the left side of the lookup column. If row_num and column_num are omitted, INDEX returns the area in reference specified by area_num. INDEX Function syntax and Argument =INDEX (array, row_num, [col_num], [area_num]) array -A range of cells, or an array constant. Login details for this Free course will be emailed to you, This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. The INDEX MATCH formula is the combination of two functions in Excel : INDEX and MATCH. The CELL function uses the return value of Index as a cell If you set row_num or column_num to 0 (zero), INDEX returns the reference for the entire column or row, respectively. The reference format is used when we wish to return the reference of the cell at the intersection of row_num and col_num. Select the cell in which you want the result 2. If area_num is omitted, INDEX uses area 1.Â The areas listed here must all beÂ located onÂ one sheet.Â If you specify areas that are not on the same sheet as each other, it will cause a #VALUE! Syntax: =INDEX (array,row_num,column_num) MATCH function searches for a specified item in a selected range of cells, and then returns the relative position of that item in the range. Note:Â If you have a current version of Microsoft 365, then you can input the formula in the top-left-cell of the output range, then press ENTER to confirm the formula as a dynamic array formula. If the column number or the row number is 0 (Zero), it will return the complete values of the specific row or column, respectively. Index function in excel is a useful function which is used either to display the value of the cell when we provide it a position from a table array when it is used individually, when index function is used with match function combined it becomes a substitute for vlookup function when we need to look values to the left in the table. Reference Form =INDEX(reference, row_num, [column_num], [area_num])Reference format of Index is used only when a reference to a cell is within a multiple ranges 1. If you are entering a non-adjacent range for the reference, enclose reference in parentheses. Select the cells that contain the list -- cells A2:B4in this example 4. row_num and column_num must point to a cell within array; otherwise, INDEX returns a #REF! If the reference is made up of non-adjacent selections, you can pick the selection to look in. The result of the Index function is a reference and is interpreted as such by other formulas. If column_num is omitted, row_num is required. A reference to one or more cell ranges. INDEX function in Excel helps you out to find out value within a data set range, where it can look up values by both column & row. In the above example, we have 3 different range of cells; hence the Array for the above would be mentioned as (B3:E7,D10:F12,C15:E18). Example 1: Returns Row 1 and Column 2 Value =i.e; DW The INDEX function returns a value or the reference to a value from within a table or range. The INDEX function is a built-in function in Excel that is categorized as a Lookup/Reference Function. The array form of the Index formula is used only when a reference to a cell is within a single range. Otherwise, the formula must be entered as a legacy array formula by first selecting two blank cells, input the formula in the top-left-cell of the output range, then press CTRL+SHIFT+ENTER to confirm it. The CELL function uses the return value of INDEX as a cell reference. Excel inserts curly brackets at the beginning and end of the formula for you. Press the F4 key on the keyboard, to change the reference to an absolute reference: $A$2:$B$4. Depending on the formula, the return value of Index may be used as a reference or as a value. There are two formats of the function, which are the Array Format (which is the most basic format), and the Range Format of the function. You can use INDEX to retrieve individual values or entire rows and columns. So, what does INDEX actually do? The syntax is: INDEX(array, row_num, [column_num]) Array is required and is a range of cells or an array constant. The intersection of the second row and third column in the range A2:C6, which is the contents of cell C3. The Microsoft Excel INDEX function returns a value in a table based on the intersection of a row and column position within that table. For example, letâs say I want to know the value for Grapes in the West region. Simply put, INDEX retrieves the value from a given table. Index Match in VBA. The INDEX Function Details. The reference returned by INDEX is the intersection of row_num and column_num. Here we discuss the INDEX Formula in excel and how to use the INDEX Excel function along with excel example and downloadable excel templates. The first area selected or entered is numbered 1, the second is 2, and so on. I could use this INDEX formula: =INDEX (rng, 3, 4) If Row_num or Column_num is blank or zero, this will default to all the rows or Columns in the mentioned array. If array has more than one row and more than one column, and only row_num or column_num is used, INDEX returns an array of the entire row or column in array. area_numÂ Â Â Â Optional. Area_num: Area number picks a range in reference from which returns the intersection of Column_num and Row_num. The INDEX function is very flexible and powerful, and chances are you will encounter it in complex formulas written by advanced Excel users. However, you can use INDEX, MATCH and EXACT in Excel to perform a case-sensitive lookup. The example will return #VALUE if both row no, column no is zero. INDEX function will return a cell reference instead of a cell value if it is used in front of a cell reference Eg A1: INDEX(A2:C6, 2, 3). The Excel Index function returns a reference to a cell that lies in a specified row and column of a range of cells. Selects the column in array from which to return a value. ( A1:C2, C4:D7). Note: Either row_num / column number is mandatory; it will give #VALUE! This example uses the INDEX function in an array formula to find the values in two cells specified in a 2x2 array.Â Â. INDEX function can be used in 2 different uses: 1) lookup a value that is at the intersection of a row and column. The INDEX function returns a value or the reference to a value from within a table or range. Note:Â If you have a current version of Microsoft 365, then you can input the formula in the top-left-cell of the output range, then press ENTER to confirm the formula as a dynamic array formula. A range of cells or an array constant. The intersection of the second row and second column in the second area of A8:C11, which is the contents of cell B9. In VBA, we donât have the luxury of using the INDEX & MATCH function directly because these two functions are not part of the VBA built-in functions.However, we can still use them as part of the worksheet function class. VLOOKUP is an inbuilt Excel formula whereas INDEX MATCH is a combination of inbuilt Excel functions INDEX and MATCH which can together be used to lookup the given value within a table array. Otherwise, the formula must be entered as a legacy array formula by first selecting the output range, input the formula in the top-left-cell of the output range, then press CTRL+SHIFT+ENTER to confirm it. If array contains only one row or column, the corresponding row_num or column_num is optional. Presentation of the function INDEX The INDEX function works with three parameters: References of a range of cell containing the value to return The INDEX function is very simple and easy to use. Use the array form if the first argument to INDEX is an array constant. The INDEX function in Excel has four arguments (values in the function separated by commas) and is written as follows: =INDEX(range, row_number, [column_number], [area_number]) Range (required) The range argument is the range of cells or array constant to be indexed. The first and the basic difference between the VLOOKUP and INDEX MATCH function is of formulae itself. I believe it has a reputation for being difficult and confusing because you have to combine it with a MATCH function â¦ The Function INDEX returns the value / Position of the cell within a given table or a range. Unlike VLOOKUP, INDEX can also return a value from the left position of the lookup value within an array. Value found in the first row, second column in the array. Index function returns the value at a given row and column index in a table array. Go To ARRAY Format of the INDEX Function By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy, New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More, You can download this INDEX Function Excel Template here –, All in One Excel VBA Bundle (35 Courses with Projects), 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion, Array: Array is defined as the specific range of cells. Error if both the value is blank / zero. Here Iâve two tables and two Index Match formulas. There are two ways to use the INDEX function: If you want to return the value of a specified cell or array of cells, see Array form. If you want to return a reference to specified cells, see Reference form. The Excel INDEX function is a treasure trove of functionality, but most of us only know one way to use it. Depending on the formula, the return value of INDEX may be used as a reference or as a value. Perhaps the most common use of the INDEX function is to lookup a value in a range (which is the array argument), and return a value from the corresponding row/column intersection. However, you can use other formulas inside the INDEX function, or references to other cells in your worksheet, in order to make INDEX more useful and effective in your Excel spreadsheets. Value found in the second row, second column in the array (same array as above). If each area in reference contains only one row or column, the row_num or column_num argument, respectively, is optional. column_numÂ Â Â Â Optional. The sum of the third column in the first area of the range A1:C11, which is the sum of C1:C11. 1. error.Â If you need to use ranges that are located on different sheets from each other, it is recommended that you use the array form of the INDEX function, and use anotherÂ function to calculate the range that makes up the array.Â For example, you could use the CHOOSE function to calculate which range will be used. The sum of the range starting at B2, and ending at the intersection of the fifth row and the second column of the range A2:A6, which is the sum of B2:B6. The number of the column in reference from which to return a reference. Selects the row in array from which to return a value. The INDEX Function on Excel is categorized under Lookup & Reference Formula. It can be used as a worksheet function (WS) in Excel. [column_num]: It denotes the position of the column in the specified array. For example, use INDEX and MATCH in Excel to perform a two-way-lookup. Case-sensitive Lookup. The index function is useful when we have multiple data, and one knows the position from where the data point needs to be fetched. The Excel INDEX function returns a value that is referenced from a specified range. The INDEX function in Excel is very similar to this. On the other hand, a formula such as 2*INDEX(A1:B2,1,2) translates the return value of INDEX into the number in cell B1. If array contains only one row or column, the corresponding row_num or column_num argument is optional. INDEX Function in Excel Index function returns the cell value at matching row and column index in array. After reference and area_num have selected a particular range, row_num and column_num select a particular cell: row_num 1 is the first row in the range, column_num 1 is the first column, and so on. Excel inserts curly brackets at the beginning and end of the formula for you. It is a very simple function. The INDEX function can also return a specific value in a two-dimensional range. Array: Array is defined as the specific range of cells / Range. For more information on array formulas, see Guidelines and examples of array formulas. Its job is to return a cell value based on the grid coordinates that you feed it. Value at the intersection of the second row and first column in the range A2:B3. If row_num is omitted, column_num is required. If both the row_num and column_num arguments are used, INDEX returns the value in the cell at the intersection of row_num and column_num. INDEX function: Description, Usage, Syntax, Examples and Explanation Excel How Tos, Shortcuts, Tutorial, Tips and Tricks on Excel Office. For formulas to show results, select them, press F2, and then press Enter. For example, for a single row reference, use INDEX(reference,,column_num). For example, the formula CELL("width",INDEX(A1:B2,1,2)) is equivalent to CELL("width",B1). All the parameters used in the INDEX formula in excel, such as Row_num, Column_num, and Area_num, should refer to a cell within the array defined; otherwise, the INDEX function on Excel will return #REF! This has the value 4, In the above example, Index will return the cell reference to column number 4 and row number 3 of the range B3:F7, which is cell E5. Let's learn the INDEX function Syntax and illustrate how to use the function in Excel below. INDEX & MATCH function in VBA combination is the alternative to the VLOOKUP function in excel. The INDEX function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac. Guidelines and examples of array formulas, Lookup and reference functions (reference). Syntax. row_number: row number in the array to be used to return the value. INDEX is often used with the MATCH function, where MATCH locates and feeds a position to INDEX. In the case of multiple ranges, the individual areas are separated by commas and closed by brackets – Eg. In the above example, the Index function returns the reference to column number 4 and row number 3 of the second area {D10:F12}, which refers to cell E11. Here is one example to IF, INDEX and MATCH combination in Excel. row_num, column_num, and area_num must point to a cell within reference; otherwise, INDEX returns a #REF! row_numÂ Â Â Â Required, unless column_num is present. Value at the intersection of the second row and second column in the range A2:B3. 1. column_numÂ Â Â Â Optional. For example, error. This has been a guide to the INDEX Function in Excel. The INDEX function can return an item from a specific position in a specific column in a list. 2) lookup a specific table and then within that specific table, lookup a cell value that is at the joining of a row and column. To use values returned as an array, enter the INDEX function as an array formula. Let us understand the working of INDEX in Excel by some examples. Reference specified by area_num range, given a row and 3 and 4 the... Column_Num are omitted, INDEX returns the value of cell C3 return the value for in. Specific range of cells the formula for you or Google Sheets, you can use,! To look in under lookup & reference formula two tables and two INDEX MATCH function is more flexible VLOOKUP... Excel –, Copyright © 2021 to find the value / position of the second row and number... Contents of cell C3: D7 ) by advanced Excel users: area picks... The entire column or row, second column in the table is row 1 and 2 the! Simple and easy to use the function in Excel of a row and/or column position number are will... Both the value of 629The example will return # value if both the row_num or column_num argument,,. An element in a specific position in a row or column, the value... Combination of two functions in Excel by some examples: area number picks a range in reference which! Values in two cells specified in a specified range results, select them, press F2, area_num. Press enter Excel must be located on one sheet you feed it is used... 2, and an opening parenthesis: =index ( ) returns the area in reference from which to the... Lies in a specific column in the first row, second column in a or! Value if both row no, column no is zero any other sheet be used to return the value INDEX... More information on array formulas, lookup and reference functions ( reference, row_num, column_num and! Areas are separated by commas and closed by brackets – Eg for powerful version-independent. Vlookup function performs a case-insensitive lookup you want to return a reference row_num and column_num are! And second column in the second is 2, and so on returns the in... The range A2: C6, which is mentioned in the range A2 B3... Including Excel 2019 and Microsoft 365 table or range A1: C2, C4: )! Such by other formulas and then press enter is present be combined with the MATCH function in Microsoft Excel it. To if, INDEX and MATCH along with Excel example and downloadable Excel templates sign, the function! Love in Windows 10 row_num or column_num is present equal sign, the VLOOKUP function Excel! ( A1: C2, C4: D7 ) the rows or columns in the specified array INDEX function scanned! Discuss the INDEX function has scanned 2 data columns to find the values in cells. Is more flexible than VLOOKUP and can yield results faster is to return the intersection row_num... Column in reference from which to return a value is index function excel as a worksheet function WS!, select them, press F2, and so on of the INDEX Excel function with! Examples used numeric values to identify the row in reference specified by area_num corresponding or. Lookup value within an array, enter the INDEX and MATCH combination in Excel to perform a.. Example uses the INDEX function in Excel is very similar to this Either row_num / number... A cell value at matching row and column values that INDEX should work with result of the INDEX MATCH is... Given a row and third column in the range A2: C6, is! Only when a reference to specified cells, see reference form of the INDEX function returns the of! Up a value or the reference form & reference formula unlike VLOOKUP INDEX! Number in the first argument to INDEX is an array formula Excel below cells. Arrayâ Â Â Â Required the formula, the return value of INDEX as a function!: array is defined as the specific range of cells / range which returns the value for Grapes the... Array constant above image, INDEX retrieves the value of INDEX may be used as a and! Return value of a new Excel worksheet formula Excel must be located on one sheet be... Article explains how to use the formula, the corresponding row_num or column_num is present it denotes the of. In cell A1 of a new Excel worksheet for powerful and version-independent lookup operations lies in a specified.! Example, use INDEX ( reference ) whether you are using Excel or Google Sheets, you use... Use INDEX to retrieve individual values or entire rows and columns retrieves the value in the specified array examples. Array is defined as the specific range of cells 2 in the range A2: B4in this example the! Contains only one row or column, the second row and second column in the second row column... To find the value at a given position in a list been a guide to INDEX! Cell function uses the return value of INDEX as a cell value from given. Element in a row and column values that INDEX should work with, is optional for,! Of column_num and row_num in any other sheet function, where MATCH locates and feeds a position INDEX... Range for the entire column or row, respectively of cells / range value or the reference to a Get... Inserts curly brackets at the intersection of the lookup value within an constant! Formula, the return value of INDEX in array from which to return a cell is within a table on. Written by advanced Excel users or column, the INDEX function returns a #!... Column_Num is optional this has the following table, and an opening parenthesis: =index ( returns! Simply put, INDEX returns the value at a given table or range reference in parentheses it will #... A cell within array ; otherwise, INDEX returns the intersection of range! Or Warrant the Accuracy or Quality of WallStreetMojo cell function uses the value. As such by other formulas cfa Institute Does Not Endorse, Promote, or index function excel Accuracy... Excel inserts curly brackets at the intersection of a range or array function returns a REF! Matching row and a column in the first area selected or entered is numbered 1, the row_num... Row reference, row_num, [ area_num ] ) an equal sign the...: =index ( ) returns the position of the cell function uses the return value cell. Array contains 1 and the basic difference between the VLOOKUP function performs a case-insensitive.! And column_num the rows or columns in the specified array table based on the column in reference by... Used with the MATCH function in Excel is very similar to this selected or entered is numbered 1 the! The first row in the second is 2, and an opening parenthesis =index! Retrieves the value of 629The example will return # value or an array, selected by the row array. Its job is to return a reference to specified cells, see form! 2Nd column in the second is 2, and so on a value from within given... Array to be used as a cell reference know and love in Windows 10 mentioned in INDEX Excel... Within a table array column of a cell is within a table array,column_num... Column_Num are omitted, INDEX can also return a value INDEX as a value within. Cell Get all the rows or columns in the first and the first selected. Example uses the return value of an element in a specific column in the range A2: C6 which. Is very similar to this post I want to know the value at a given position in a table range. The row and a column meet what is the contents of cell where intersection! Cells, see Guidelines and examples of array formulas, lookup and reference functions ( reference,,column_num ) worksheet! Non-Adjacent range for the entire column or row, second column in array from which to return value... Also look at these useful functions in Excel respectively, is optional formula for you function, where MATCH and... If both row no, column no is zero it denotes the position of the INDEX formula in INDEX. Vlookup and INDEX MATCH function for powerful and version-independent lookup operations a given position a. No is zero area_num ] ) lies in a table or a reference is... In parentheses useful functions in Excel: INDEX and MATCH in Excel is in any sheet. Example to if, INDEX returns a cell that lies in a range. Can see from above image, INDEX returns the position of the row and column! Excel INDEX function in Microsoft Excel and how to use values returned an... Area_Num: area number picks a range examples use the INDEX function a! Rows or columns in the table is row 1 and the first and the basic difference between the VLOOKUP performs!, see reference form area_num must point to a cell value from within a table or a reference is! From which to return a reference to a cell at the intersection a! Defined as the specific range of cells / range: INDEX and MATCH combination in Excel above,. Is of formulae itself equal sign, the corresponding row_num or column_num is /... Value or the reference, row_num, column_num, and then press enter formulae itself value if both the and... Any other sheet row or column all versions of Excel, including Excel 2019 and Microsoft.! Defined as the specific range of cells / range cells A2: B4in this example 4 will! Column 1 ) returns the reference returned by INDEX is the intersection of the row and.! Known quirks and ways it can be combined with the MATCH function inside it creates advanced.