What is INDEX function in Excel? INDEX function is one of Lookup and Reference functions in Microsoft Excel that returns a value or the reference to a value from within a table or range. There are two ways to use the INDEX function: If you want to return a reference to specified cells, Click to see Reference form If you want to…
Tag: INDEX function
Basic text sort formula in Excel
To dynamically sort text values in alphabetical order, you can use use a formula based on the COUNTIF function. Formula =COUNTIF(range,”<=”&A1) Explanation In the example shown, the formula in C5 is: =COUNTIF(countries,”<=”&B5) where “countries” is the named range B4:B13 How this formula works This formula uses the “greater than or equal to” operator with text, something you might not have tried before.…
How to calculate two-way lookup VLOOKUP in Excel Table
To do a two-way lookup in an Excel Table, you can use the MATCH function with a structured reference and VLOOKUP. See example below: Recall that VLOOKUP depends on the lookup value being to the left of the value being retrieved in a table. Generally, this means the lookup value will be the first value in the table. If you have data where…
Example of COUNTIFS with variable table column in Excel
To use COUNTIFS with a variable table column, you can use INDEX and MATCH to find and retrieve the column for COUNTIFS. See example below: Formula =COUNTIFS(INDEX(Table,0,MATCH(name,Table[#Headers],0)),criteria)) Explanation In the example shown, the formula in H5 is: =COUNTIFS(INDEX(Table1,0,MATCH(G5,Table1[#Headers],0)),”x”) How this formula works First, for context, it’s important to note that you can use COUNTIFS with a regular structured reference like…
How to create running total in an Excel Table
This tutorial shows illustrates a Running total in Excel Table. To create a running total in an Excel Table, you can use the INDEX function set up with a structured reference. Formula =SUM(INDEX([column],1):[@column]) Explanation In the example shown, the formula in F5 is: =SUM(INDEX([Total],1):[@Total]) When copied down the column, this formula will return a running total at each row. How this…
How to calculate average last N values in a table in Excel
To calculate the average for the last N values n an Excel table (i.e. last 3 rows, last 5 rows, etc.) you can use the AVERAGE function together with the INDEX and ROWS functions. See example below: Formula =AVERAGE(INDEX(table[column],ROWS(table)-(N-1)):INDEX(table[column],ROWS(table))) Explanation In the example shown, the formula in F5 is: =AVERAGE(INDEX(Table1[Sales],ROWS(Table1)-(F4-1)):INDEX(Table1[Sales],ROWS(Table1))) How this formula works This formula is a good example…
Get column name from index in Excel Table
To get the name of a column in an Excel Table from its numeric index, you can use the INDEX function with a structured reference. See example below: Formula =INDEX(Table[#Headers],index) Explanation In the example shown, the formula in I4 is: =INDEX(Table1[#Headers],H5) When the formula is copied down, it returns an name for each column, based on index values in column…
Last row number in range
You can get the last row in a range with a formula based on the ROW function. See example below; Formula =MIN(ROW(range))+ROWS(range)-1 Explanation In the example shown, the formula in cell F5 is: =MIN(ROW(data))+ROWS(data)-1 where data is a named range for B5:D10 How this formula works When given a single cell reference, the ROW function returns the row number for…
How to get last column number in range in Excel
You can get the last column in a range with a formula based on the COLUMN function. See example below: Formula =MIN(COLUMN(range))+COLUMNS(range)-1 Note: When given a single cell reference, the COLUMN function returns the column number for that reference. However, when given a range that contains multiple columns, the COLUMN function will return an array that contains all column numbers…
How to get relative row numbers in a range in Excel
To get a full set of relative row numbers in a range, you can use an array formula based on the ROW function. See example below; Formula {=ROW(range)-ROW(range.firstcell)+1} Note: this is an array formula that must be entered with Control + Shift + Enter. If you’re entering this on the worksheet (and not inside another formula), make a selection that includes more…
How to get first row number in range in Excel
You can get the first row (i.e. the starting row number) in a range with a formula based on the ROW function. Formula =MIN(ROW(range)) Explanation In the example shown, the formula in cell F5 is: =MIN(ROW(data)) where data is a named range for B5:D10 How this formula works When given a single cell reference, the ROW function returns the row…
How to retrieve first match between two ranges in Excel
To retrieve the first match in two ranges of values, you can use a formula based on the INDEX, MATCH, and COUNTIF functions. Formula =INDEX(range2,MATCH(TRUE,COUNTIF(range1,range2)>0,0)) Explanation In the example shown, the formula in G5 is: =INDEX(range2,MATCH(TRUE,COUNTIF(range1,range2)>0,0)) where “range1” is the named range B5:B8, “range2” is the named range D5:D7. How this formula works In this example the named range “range1” refers to cells B5:B8,…
How to create dynamic named range with INDEX in Excel
This tutorials show examples one and two dynamic named ranges created. The first is created with the INDEX function together with the COUNTA function. Dynamic named ranges automatically expand and contract when data is added or removed. Formula =$A$1:INDEX($A:$A,lastrow) Explanation This page shows an example of a dynamic named range created with the INDEX function together with the COUNTA function. Dynamic named ranges automatically expand…
How to get random value from list or table in Excel
To get a random value from a table or list in Excel, you can use the INDEX function with help from the RANDBETWEEN and ROWS functions. Formula =INDEX(data,RANDBETWEEN(1,ROWS(data)),1) Note: this formula uses the named range “data” (B5:E104) for readability and convenience. If you don’t want to use a named range, substitute $B$5:$E$104 instead. Explanation In the example shown, the formula…
How to generate random number weighted probability in Excel
To generated a random number, weighted with a given probability, you can use a helper table together with a formula based on the RAND and MATCH functions. Formula =MATCH(RAND(),cumulative_probability) Explanation In the example shown, the formula in F5 is: =MATCH(RAND(),D$5:D$10) How this formula works This formula relies on the helper table visible in the range B4:D10. Column B contains the…
List sheet names with formula in Excel
To list worksheets in an Excel workbook, you can use a 2-step approach: (1) define a named range called “sheetnames” with an old macro command and (2) use an INDEX formula to retrieve sheet names using the named range. Formula =GET.WORKBOOK(1)&T(NOW()) Note: because this formula relies on a macro command, you’ll need to save as a macro-enabled workbook if you want the formula…
Find most frequent text within a range with criteria in Excel
To find the most frequently occurring text in a range, based on criteria you supply, you can use an array formula based on several Excel functions MATCH, MODE, INDEX, and IF. Formula =INDEX(range1,MODE(IF(range2=criteria, MATCH(range1,range1,0)))) Note: this is an array formula and must be entered with control + shift + enter. Explanation In the example shown, the formula in G5 is: =INDEX(supplier,MODE(IF(client=F5, MATCH(supplier,supplier,0))))…
Extract most frequently occurring text in Excel
To extract the word or text value that occurs most frequently in a range, you can use a formula based on several functions INDEX, MATCH, and MODE. Formula =INDEX(range,MODE(MATCH(range,range,0))) Explanation In the example shown, the formula in H5 is: =INDEX(B5:F5,MODE(MATCH(B5:F5,B5:F5,0))) Working from the inside out, the MATCH function matches the range against itself. That is, we give the MATCH function…
How to find and replace multiple values at same time in Excel
To find and replace multiple values with a formula, you can nest multiple SUBSTITUTE functions together, and feed in find/replace pairs from another table using the INDEX function. Formula =SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)), INDEX(find,2),INDEX(replace,2)) Explanation In the example shown, we are performing 4 separate find and replace operations. The formula in G5 is: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1), INDEX(replace,1)),INDEX(find,2),INDEX(replace,2)), INDEX(find,3),INDEX(replace,3)),INDEX(find,4),INDEX(replace,4)) where “find” is the named range E5:E8, and “replace” is…
How to calculate next scheduled event in Excel
To get the next scheduled event from a list of events with dates, you can use an array formula based on the MIN and TODAY functions to find the next date, and INDEX and MATCH to display the event on that date. Formula {=MIN(IF((range>=TODAY()),range))} Note: this is an array formula and must be entered with Control + Shift + Enter. Explanation In…
Categorize text with keywords in Excel
This tutorial shows how to Categorize text with keywords in Excel using the example below; Formula {=INDEX(categories,MATCH(TRUE,ISNUMBER (SEARCH(keywords,text)),0))} Explanation To categorize text using keywords with a “contains” match, you can use the SEARCH function, with help from INDEX and MATCH. In the example shown, the formula in C5 is: {=INDEX(categories,MATCH(TRUE, ISNUMBER(SEARCH(keywords,B5)),0))} where “keywords” is the named range E5:E14, and “categories” is the…
Two-way lookup with INDEX and MATCH in Excel
This tutorial shows how to calculate Two-way lookup with INDEX and MATCH in Excel using the example below; Formula =INDEX(data,MATCH(val,rows,1),MATCH(val,columns,1)) Explanation To lookup in value in a table using both rows and columns, you can build a formula that does a two-way lookup with INDEX and MATCH. In the example shown, the formula in J8 is: =INDEX(C6:G10,MATCH(J6,B6:B10,1),MATCH(J7,C5:G5,1)) Note that this…
Sum range with INDEX in Excel
This tutorial shows how to calculate Sum range with INDEX in Excel using the example below; Formula =SUM(INDEX(data,0,column)) Explanation To sum all values in a column or row, you can use the INDEX function to retrieve the values, and the SUM function to return the sum. This technique is useful in situations where the row or column being summed is dynamic, and changes based…
Position of first partial match in Excel
This tutorial shows how to work Position of first partial match using the example below; Formula =MATCH(“*text*”,range,0) Explanation To get the position of the first partial match (i.e. the cell that contains text you are looking for) you can use the MATCH function with wildcards. In the example shown, the formula in E8 is: =MATCH(“*”&E7&”*”,B6:B11,0) How this formula works The MATCH function returns the…
Next largest match with the MATCH function in Excel
This tutorial shows how to calculate Next largest match with the MATCH function in Excel using the example below; Formula =MATCH(value,array,-1) Explanation To lookup the “next largest” match in a set of values, you can use the MATCH function in approximate match mode, with -1 for match type. In the example shown, the formula in F7 is: =MATCH(F6,length,-1) where “length” is the…
Multi-criteria lookup and transpose in Excel
This tutorial shows how to work Multi-criteria lookup and transpose in Excel using the example below; Formula {=INDEX(range1,MATCH(1,($A1=range2)*(B$1=range3),0))} Explanation To perform a multi-criteria lookup and transpose results into a table, you can use an array formula based on INDEX and MATCH. In the example shown, the formula in G5 is: {=INDEX(amount,MATCH(1,($F5=location)*(G$4=date),0))} Note this formula is an array formula and must be entered with control +…
Match next highest value in Excel
This tutorial shows how to Match next highest value in Excel using the example below; Formula =INDEX(data,MATCH(lookup,values)+1) Explanation To match the “next highest” value in a lookup table, you can use a formula based on INDEX and MATCH. In the example shown, the formula in F6 is: =INDEX(level,MATCH(F4,points)+1) where “level” is the named range C5:C9, and “points” is the named…
Lookup lowest value in Excel
This tutorial shows how to Lookup lowest value in Excel using the example below; Formula =INDEX(range,MATCH(MIN(vals),vals,0)) Explanation To lookup information associated with the lowest value in table, you can use a formula based on INDEX, MATCH, and MIN functions. In the example shown, a formula is used to identify the name of the contractor with the lowest bid. The formula…
Lookup entire row in Excel
This tutorial shows how to Lookup entire row in Excel using the example below; Formula =INDEX(data,MATCH(value,array,0),0) Explanation To lookup and retrieve an entire row, you use the INDEX and MATCH functions. In the example shown, the formula used to look up all values for the Central region is: =INDEX(C5:F8,MATCH(H5,B5:B8,0),0) How this formula works The gist: use MATCH to identify the…
INDEX and MATCH with multiple criteria in Excel
This tutorial shows how to calculate INDEX and MATCH with multiple criteria in Excel using the example below; Formula {=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0))} Explanation To lookup values with INDEX and MATCH, using multiple criteria, you can use an array formula. In the example shown, the formula in H8 is: {=INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0))} Note: this is an array formula, and must be entered with control + shift…
Index and match on multiple columns in Excel
This tutorial shows how to calculate Index and match on multiple columns in Excel using the example below; Formula {=INDEX(range1,MATCH(1,MMULT(–(range2=critera), TRANSPOSE(COLUMN(range2)^0)),0))} Explanation To lookup a value by matching across multiple columns, you can use an array formula based on the MMULT, TRANSPOSE, COLUMN, and INDEX. In the example shown, the formula in H4 is: {=INDEX(groups,MATCH(1,MMULT(–(names=G4), TRANSPOSE(COLUMN(names)^0)),0))} where “names” is the named range C4:E7, and…
Join tables with INDEX and MATCH in Excel
This tutorial shows how to Join tables with INDEX and MATCH in Excel using the example below; Formula =INDEX(data,MATCH(lookup,ids,0),2) Explanation To join or merge tables that have a common id, you can use the INDEX and MATCH functions. In the example shown, the formula in E5 is: =INDEX(data,MATCH($C5,ids,0),2) where “data” is the named range H5:J8 and “ids” is the named range H5:H8.…
INDEX and MATCH descending order in Excel
This tutorial shows how to calculate INDEX and MATCH descending order in Excel using the example below; Formula =INDEX(range1,MATCH(lookup,range2,-1)) Explanation To retrieve values from a table where lookup values are sorted in descending order [Z-A] you can use INDEX and MATCH, with MATCH configured for approximate match using a match type of -1. In the example shown, the formula in F5…
Get nth match with INDEX / MATCH in Excel
This tutorial shows how to Get nth match with INDEX / MATCH in Excel using the example below; Formula {=INDEX(array,SMALL(IF(vals=val,ROW(vals)-ROW(INDEX(vals,1,1))+1),nth))} Explanation To retrieve multiple matching values from a set of data with a formula, you can use the IF and SMALL functions to figure out the row number of each match and feed that value back to INDEX. In the example shown, the…
Lookup entire column in Excel
This tutorial shows how to Lookup entire column in Excel using the example below; Formula =INDEX(data,0,MATCH(value,headers,0)) Explanation To lookup and retrieve an entire column, you can use a formula based on the INDEX and MATCH functions. In the example shown, the formula used to lookup all Q3 results is: =INDEX(C5:F8,0,MATCH(I5,C4:F4,0)) Note: this formula is embedded in the SUM function only…
Lookup and sum column in Excel
This tutorial shows how to Lookup and sum column in Excel using the example below; Formula =SUM(INDEX(data,0,MATCH(val,header,0))) Explanation To lookup and return the sum of a column, you can use the a formula based on the INDEX, MATCH and SUM functions. In the example shown, the formula in I7 is: =SUM(INDEX(C5:F11,0,MATCH(I6,C4:F4,0))) How this formula works The core of this formula…
Get information corresponding to max value in Excel
This tutorial shows how to Get information corresponding to max value in Excel using the example below; Formula =INDEX(range1,MATCH(MAX(range2),range2,0) Explanation To lookup information related to the maximum value in a range, you can use a formula that combines the MAX, INDEX, and MATCH functions. In the example shown, the formula in I8 is: =INDEX(F3:F11,MATCH(MAX(C3:C11),C3:C11,0)) Which returns the number 3920, representing…
Get first match cell contains in Excel
This tutorial shows how to Get first match cell contains in Excel using the example below; Formula {=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0))} Explanation To check a cell for one of several things, and return the first match found in the list, you can use an INDEX / MATCH formula that uses SEARCH or FIND to locate a match. This is an array formula and…
Get first non-blank value in a list in Excel
This tutorial shows how to Get first non-blank value in a list in Excel using the example below; Formula {=INDEX(range,MATCH(FALSE,ISBLANK(range),0))} Explanation If you need to get the first non-blank value (text or number) in a in a one-column range you can use an array formula based on the INDEX, MATCH, and ISBLANK functions. In the example the formula we’re using…
Get address of lookup result in Excel
This tutorial shows how to Get address of lookup result in Excel using the example below; Formula =CELL(“address”,INDEX(range,row,col)) Explanation To get the address of a lookup result derived with INDEX, you can use the CELL function. In the example shown, the formula in cell G8 is: =CELL(“address”,INDEX(B5:D11,MATCH(G6,B5:B11,0),2)) Which returns an address of $C$8, the address of the cell returned by…