INDEX function in Excel - 6 most efficient uses

In this tutorial, you will find a number of formula examples that demonstrate the most efficient uses of INDEX in Excel.

Of all Excel functions whose power is often underestimated and underutilized, INDEX would definitely rank somewhere in the top 10. In the meantime, this function is smart, supple and versatile.

So, what is the INDEX function in Excel? Essentially, an INDEX formula returns a cell reference from within a given array or range. In other words, you use INDEX when you know (or can calculate) the position of an element in a range and you want to get the actual value of that element.

This may sound a bit trivial, but once you realize the real potential of the INDEX function, it could make crucial changes to the way you calculate, analyze and present data in your worksheets.

Excel INDEX function - syntax and basic uses

There are two versions of the INDEX function in Excel - array form and reference form. Both forms can be used in all versions of Microsoft Excel 2019, 2016, 2013, 2010, 2007 and 2003.

INDEX array form

The INDEX array form returns the value of an element in a table or an array based on the row and column numbers you specify.

INDEX(array, row_num, [column_num])
• array - is a range of cells, named range, or table.
• row_num - is the row number in the array from which to return a value. If row_num is omitted, column_num is required.
• column_num - is the column number from which to return a value. If column_num is omitted, row_num is required.

For example, the formula `=INDEX(A1:D6, 4, 3)` returns the value at the intersection of the 4th row and 3rd column in range A1:D6, which is the value in cell C4.

To get an idea of how the INDEX formula works on real data, please have a look at the following example:

Instead of entering the row and column numbers in the formula, you can supply the cell references to get a more universal formula: `=INDEX(\$B\$2:\$D\$6, G2, G1)`

So, this INDEX formula returns the number of items exactly at the intersection of the product number specified in G2 (row_num) and week number entered in cell G1 (column_num).

Tip. The use of absolute references (\$B\$2:\$D\$6) instead of relative references (B2:D6) in the array argument makes it easier to copy the formula to other cells. Alternatively, you can convert a range to a table (Ctrl + T) and refer to it by the table name.

INDEX array form - things to remember

1. If the array argument consists of only one row or column, you may or may not specify the corresponding row_num or column_num argument.
2. If the array argument includes more than one row and row_num is omitted or set to 0, the INDEX function returns an array of the entire column. Similarly, if array includes more than one column and the column_num argument is omitted or set to 0, the INDEX formula returns the entire row. Here's a formula example that demonstrates this behavior.
3. The row_num and column_num arguments must refer to a cell within array; otherwise, the INDEX formula will return the #REF! error.

INDEX reference form

The reference form of the Excel INDEX function returns the cell reference at the intersection of the specified row and column.

INDEX(reference, row_num, [column_num], [area_num] )
• reference - is one or several ranges.

If you are entering more than one range, separate the ranges by commas and enclose the reference argument in parentheses, for example (A1:B5, D1:F5).

If each range in reference contains only one row or column, the corresponding row_num or column_num argument is optional.

• row_num - the row number in the range from which to return a cell reference, it's similar to the array form.
• column_num - the column number from which to return a cell reference, also works similarly to the array form.
• area_num - an optional parameter that specifies which range from the reference argument to use. If omitted, the INDEX formula will return the result for the first range listed in reference.

For example, the formula `=INDEX((A2:D3, A5:D7), 3, 4, 2)` returns the value of cell D7, which is at the intersection of the 3rd row and 4th column in the second area (A5:D7).

INDEX reference form - things to remember

1. If the row_num or column_num argument is set to zero (0), an INDEX formula returns the reference for the entire column or row, respectively.
2. If both row_num and column_num are omitted, the INDEX function returns the area specified in the area_num argument.
3. All of the _num arguments (row_num, column_num and area_num) must refer to a cell within reference; otherwise, the INDEX formula will return the #REF! error.

Both of the INDEX formulas we've discussed so far are very simple and only illustrate the concept. Your real formulas are likely to be far more complex than that, so let's explore a few most efficient uses of INDEX in Excel.

How to use INDEX function in Excel - formula examples

Perhaps there aren't many practical uses of Excel INDEX by itself, but in combination with other functions such as MATCH or COUNTA, it can make very powerful formulas.

Source data

All of our INDEX formulas (except for the last one), we will use the below data. For convenience purposes, it is organized in a table named SourceData.

The use of tables or named ranges can make formulas a bit longer, but it also makes them significantly more flexible and better readable. To adjust any INDEX formula for your worksheets, you need only to modify a single name, and this fully makes up for a longer formula length.

Of course, nothing prevents you from using usual ranges if you want to. In this case, you simply replace the table name SourceData with the appropriate range reference.

1. Getting the Nth item from the list

This is the basic use of the INDEX function and a simplest formula to make. To fetch a certain item from the list, you just write `=INDEX(range, n)` where range is a range of cells or a named range, and n is the position of the item you want to get.

When working with Excel tables, you can select the column using the mouse and Excel will pull the column's name along with the table's name in the formula:

To get a value of the cell at the intersection of a given row and column, you use the same approach with the only difference that you specify both - the row number and the column number. In fact, you already saw such a formula in action when we discussed INDEX array form.

And here's one more example. In our sample table, to find the 2nd biggest planet in the Solar system, you sort the table by the Diameter column, and use the following INDEX formula:

`=INDEX(SourceData, 2, 3)`

• `Array` is the table name, or a range reference, SourceData in this example.
• `Row_num` is 2 because you are looking for the second item in the list, which is in the 2nd
• `Column_num` is 3 because Diameter is the 3rd column in the table.

If you want to return the planet's name rather than diameter, change column_num to 1. And naturally, you can use a cell reference in the row_num and/or column_num arguments to make your formula more versatile, as demonstrated in the screenshot below:

2. Getting all values in a row or column

Apart from retrieving a single cell, the INDEX function is able to return an array of values from the entire row or column. To get all values from a certain column, you have to omit the row_num argument or set it to 0. Likewise, to get the entire row, you pass empty value or 0 in column_num.

Such INDEX formulas can hardly be used on their own, because Excel is unable to fit the array of values returned by the formula in a single cell, and you would get the #VALUE! error instead. However, if you use INDEX in conjunction with other functions, such as SUM or AVERAGE, you will get awesome results.

For example, you could use the following formula to calculate the average planet temperature in the Solar system:

`=AVERAGE(INDEX(SourceData, , 4))`

In the above formula, the column_num argument is 4 because Temperature in the 4th column in our table. The row_num parameter is omitted.

In a similar manner, you can find the minimum and maximum temperatures:

`=MAX(INDEX(SourceData, , 4))`

`=MIN(INDEX(SourceData, , 4))`

And calculate the total planet mass (Mass is the 2nd column in the table):

`=SUM(INDEX(SourceData, , 2))`

From practical viewpoint, the INDEX function in the above formula is superfluous. You can simply write `=AVERAGE(range)` or `=SUM(range)` and get the same results.

When working with real data, this feature may prove helpful as part of more complex formulas you use for data analysis.

3. Using INDEX with other functions (SUM, AVERAGE, MAX, MIN)

From the previous examples, you might be under an impression that an INDEX formula returns values, but the reality is that it returns a reference to the cell containing the value. And this example demonstrates the true nature of the Excel INDEX function.

Since the result of an INDEX formula is a reference, we can use it within other functions to make a dynamic range. Sounds confusing? The following formula will make everything clear.

Suppose you have a formula `=AVERAGE(A1:A10)` that returns an average of the values in cells A1:A10. Instead of writing the range directly in the formula, you can replace either A1 or A10, or both, with INDEX functions, like this:

`=AVERAGE(A1 : INDEX(A1:A20,10))`

Both of the above formulas will deliver the same result because the INDEX function also returns a reference to cell A10 (row_num is set to 10, col_num omitted). The difference is that the range is the AVERAGE / INDEX formula is dynamic, and once you change the row_num argument in INDEX, the range processed by the AVERAGE function will change and the formula will return a different result.

Apparently, the INDEX formula's route appears overly complicated, but it does have practical applications, as demonstrated in the following examples.

Example 1. Calculate average of the top N items in the list

Let's say you want to know the average diameter of the N biggest planets in our system. So, you sort the table by Diameter column from largest to smallest, and use the following Average / Index formula:

`=AVERAGE(C5 : INDEX(SourceData[Diameter], B1))`

Example 2. Sum items between the specified two items

In case you want to define the upper-bound and lower-bound items in your formula, you just need to employ two INDEX functions to return the first and the last item you want.

For example, the following formula returns the sum of values in the Diameter column between the two items specified in cells B1 and B2:

`=SUM(INDEX(SourceData[Diameter],B1) : INDEX(SourceData[Diameter], B2))`

4. INDEX formula to create dynamic ranges and drop-down lists

As it often happens, when you start organizing data in a worksheet, you may not know how many entries you will eventually have. It's not the case with our planets table, which seems to be complete, but who knows...

Anyway, if you have a changing number of items in a given column, say from A1 to An, you may want to create a dynamic named range that includes all cells with data. At that, you want the range to adjust automatically as you add new items or delete some of the existing ones. For example, if you currently have 10 items, your named range is A1:A10. If you add a new entry, the named range automatically expands to A1:A11, and if you change your mind and delete that newly added data, the range automatically reverts to A1:A10.

The main advantage of this approach is that you do not have to constantly update all formulas in your workbook to ensure they refer to correct ranges.

One way to define a dynamic range is using Excel OFFSET function:

`=OFFSET(Sheet_Name!\$A\$1, 0, 0, COUNTA(Sheet_Name!\$A:\$A), 1)`

Another possible solution is to use Excel INDEX together with COUNTA:

`=Sheet_Name!\$A\$1:INDEX(Sheet_Name!\$A:\$A, COUNTA(Sheet_Name!\$A:\$A))`

In both formulas, A1 is the cell containing the first item of the list and the dynamic range produced by both formulas will be identical.

The difference is in the approaches. While the OFFSET function moves from the starting point by a certain number of rows and/or columns, INDEX finds a cell at the intersection of a particular row and column. The COUNTA function, used in both formulas, gets the number of non-empty cells in the column of interest.

In this example, there are 9 non-blank cells in column A, so COUNTA returns 9. Consequently, INDEX returns \$A\$9, which is the last used cell in column A (usually INDEX returns a value, but in this formula, the reference operator (:) forces it to return a reference). And because \$A\$1 is our starting point, the final result of the formula is the range \$A\$1:\$A\$9.

The following screenshot demonstrates how you can use such Index formula to create a dynamic drop-down list.

Tip. The easiest way to create a dynamically updated drop-down list is to make a named list based on a table. In this case, you won't need any complex formulas since Excel tables are dynamic ranges per se.

You can also use the INDEX function to create dependent drop-down lists and the following tutorial explains the steps: Making a cascading drop-down list in Excel.

5. Powerful Vlookups with INDEX / MATCH

Performing vertical lookups - this is where the INDEX function truly shines. If you have ever tried using Excel VLOOKUP function, you are well aware of its numerous limitations, such as inability to pull values from columns to the left of the lookup column or 255 chars limit for a lookup value.

The INDEX / MATCH liaison is superior to VLOOKUP in many respects:

• No problems with left vlookups.
• No limit to the lookup value size.
• No sorting is required (VLOOKUP with approximate match does require sorting the lookup column in ascending order).
• You are free to insert and remove columns in a table without updating every associated formula.
• And the last but not the least, INDEX / MATCH does not slow down your Excel like multiple Vlookups do.

You use INDEX / MATCH in the following way:

=INDEX (column to return a value from, (MATCH (lookup value, column to lookup against, 0))

For example, if we flip our source table so that Planet Name becomes the right-most column, the INDEX / MATCH formula still fetches a matching value from the left-hand column without a hitch.

For more tips and formula example, please see the Excel INDEX / MATCH tutorial.

6. Excel INDEX formula to get 1 range from a list of ranges

Another smart and powerful use of the INDEX function in Excel is the ability to get one range from a list of ranges.

Suppose, you have several lists with a different number of items in each. Believe me or not, you can calculate the average or sum the values in any selected range with a single formula.

First off, you create a named range for each list; let it be PlanetsD and MoonsD in this example:

I hope the above image explains the reasoning behind the ranges' names : ) BTW, the Moons table is far from complete, there are 176 known natural moons in our Solar System, Jupiter alone has 63 currently, and counting. For this example, I picked random 11, well... maybe not quite random - moons with the most beautiful names : )

Please excuse the digression, back to our INDEX formula. Assuming that PlanetsD is your range 1 and MoonsD is range 2, and cell B1 is where you put the range number, you can use the following Index formula to calculate the average of values in the selected named range:

`=AVERAGE(INDEX((PlanetsD, MoonsD), , , B1))`

Please pay attention that now we are using the Reference form of the INDEX function, and the number in the last argument (area_num) tells the formula which range to pick.

In the screenshot below, area_num (cell B1) is set to 2, so the formula calculates the average diameter of Moons because the range MoonsD comes 2nd in the reference argument.

If you work with multiple lists and don't want to bother remembering the associated numbers, you can employ a nested IF function to do this for you:

`=AVERAGE(INDEX((PlanetsD, MoonsD), , , IF(B1="planets", 1, IF(B1="moons", 2))))`

In the IF function, you use some simple and easy-to-remember list names that you want your users to type in cell B1 instead of numbers. Please keep this in mind, for the formula to work correctly, the text in B1 should be exactly the same (case-insensitive) as in the IF's parameters, otherwise your Index formula will throw the #VALUE error.

To make the formula even more user-friendly, you can use Data Validation to create a drop-down list with predefined names to prevent spelling errors and misprints:

Finally, to make your INDEX formula absolutely perfect, you can enclose it in the IFERROR function that will prompt the user to choose an item from the drop-down list if no selection has been made yet:

`=IFERROR(AVERAGE(INDEX((PlanetsD, MoonsD), , , IF(B1="planet", 1, IF(B1="moon", 2)))), "Please select the list!")`

This is how you use INDEX formulas in Excel. I am hopeful these examples showed you a way to harness the potential of the INDEX function in your worksheets. Thank you for reading!

You may also be interested in

76 responses to "INDEX function in Excel - 6 most efficient uses"

1. Sid says:

trying to use an existing spreadsheet's details to populate a different workbook dynamically, so that as the source workbook is updated (new lines inserted between existing data lines) these details transfer to the second workbook. The formula suggested is =INDEX('[MASTER Cluster Data Set.xlsx]Portfolio Clusters'!C4,1). The second workbook recognises that there is a new line to deal with but does not automatically display the row or details. Any suggestions what I am doing wrong? e-mail response would be appreciated.

• Fedor Shihantsov (Ablebits.com Team) says:

Hello Sid,
Try use a named range with a few rows.
As sample: =INDEX(FirstBook.xlsx!ANYRANGE,6)

2. NKOSI says:

Hi there, I would like to find out how can i alternate functions such as Sum, Average etc when i have 10 rows with prices or values

3. NKOSI says:

Hi there, I would like to find out how can i add a drop down list which will enable me to alternate functions such as Sum, Average etc when i have 10 values to add together or average?

4. Erik says:

I am working with a similar formula with CTRL+SHIFT+ENTER and it works on the first cell, but when I copy down I get #NUM! errors?

{=INDEX(\$B\$2:\$B\$8, SMALL(IF(\$A\$11=\$A\$2:\$A\$8, ROW(\$A\$2:\$A\$8)-ROW(\$A\$2)+1), ROW(1:1)))}

Any ideas? Would be greatly appreciated!

• Hello, Erik,

For us to be able to assist you better, please send us a small sample table with your data in Excel and the result you expect to support@ablebits.com.

• Kiritawhiti Te Rangi says:

Hi, whatever row your formula is in, use this at the end of your formula ROWS(\$C\$1:C1)... If your formula is being built inside of c1

• Mahaboob Hossain says:

5. Tariq says:

Hi,

I failed to calculate via Index function the "Min" of dates array;

=INDEX(MIN('Dates'!N15:N35),MATCH(A4,'Students'!B15:B35,0))

Result is #N/A, though values are available in the date type and both respective cells.

Regards,
Tariq

• Fedor Shihantsov (Ablebits.com Team) says:

Hello Tariq,

MIN function returns one value, so the second parameter of the INDEX function should by equal 1.

6. Brian says:

I have a large number of data in which I am trying to calculate weeks of supply. I am looking for a formula that will go to a specified cell (that contains my build for a specific category) then multiply last weeks sales by that build. If that does not equal 0, then I would like it to do the same thing and move on to the next cell and perform the same formula. Then continue until my result is zero, and finally count all the cells it performed this formula, giving me my weeks of supply.

• Hello, Brian,

7. Julie Connor says:

Hi, I have an Index Match formula: =INDEX(AI1233:AU1234,MATCH(AH1196,AI1233:AU1233,0), MATCH(AI1223,AI1233:AI1234,0)) the AH1196 is a drop down list of Jan 2016-Dec 2016. The budget data it pulls looks like this:

Date Jan-16 Feb-16 Mar-16
Postage xxxx xxxx xxxx

When I have Jan-16 in the drop down field, it works great. When I change the drop down to any other month, I get #REF!. Not sure how to enable the use of the drop down in the formula.

Any help will be greatly appreciated.

8. Julienne says:

What if I have a long time-series organized in by year (column 1) and month (column 2) and the value (column 3) and I want to compute seasonal averages (i.e. DJF, MAM, JJA, SON) over the entire time-series. How can I use the EXCEL average function to know to skip every 12th entry in generating the seasonal averages?

9. Max says:

Hi Everyone. I am trying to get the min number from these set of value from Cell, D13 to D19.
With the method i use, it is going to be very tedious as i need to repeat it till D19.

Is there a way to rewrite this formula into an array where the formula will gather the number from D13-D19 and output the minimum?

=MIN(INDEX(\$D\$2:\$LK\$3,MATCH(\$M\$5,\$B\$2:\$B\$186,0),MATCH(D13,\$D\$1:\$LK\$1,0)),INDEX(\$D\$2:\$LK\$3,MATCH(\$M\$5,\$B\$2:\$B\$186,0),MATCH(D14,\$D\$1:\$LK\$1,0)))

Thank you!

10. Jul says:

I use offset =OFFSET(\$G\$2,MATCH(M3,\$G\$3:\$G\$15,0),1) and I get #N/A for the id that didn't match is there a way to output 0(zero) or Null instead of #n/a?

11. Jul says:

I have this file:
data1 data2 data3 data4 default average new data
44.44 26.75 83.80 68.36 33.00 65.53 65.53
I need to use sumif to sum 3 of the five data rows omitting the the highest and the lowest values

12. Jul says:

data1 data2 data3 data4 default average new data
44.44 26.75 83.80 68.36 33.00 65.53 65.53
i have to use sumif to add 3 of the data rows, omitting the highest and the lowest values.
Thanks

13. Harry says:

Hi...can anyone help me use INDEX function to return a value and then drag it down to return values from other sheets in the same column ?

14. Ismail says:

I am maintaining a speadsheet which which monitors materials delivery (detailed and summary).
I have 2 worksheets (sheet1 is for detailed report & sheet2 is for summary report) in a workbook. What I want is a formula to automatically write "Delivered" in the Summary Report if all items (2 items each PO number in the example shown) are "Delivered" in the Detailed Report; and write "Partial" if one is "Delivered" and the remaining is "Undelivered".

Both tables are Named Tables, so the number of items will increase in time.

I tried to search in the forums but failed to get what I'm looking for. Would appreciate any help.

Sheet1-Detail
PONo. Project ProjCode Vendor Material Status
1 Project 1 Prj001 ABC Material 1 Delivered
1 Project 1 Prj001 ABC Material 2 Undelivered
2 Project 1 Prj001 XYZ Material 3 Delivered
2 Project 1 Prj001 XYZ Material 4 Delivered

Sheet2-Summary
PONo. Project ProjCode Vendor Status
1 Project 1 Prj001 ABC Partial
2 Project 1 Prj001 XYZ Delivered

15. Gesyca says:

Good morning I am trying to write an INDEX MATCH Formula that will dynamically set the INDEX lookup column based on the header column.

what I have are 2 tabs which house my base data; we will call them Table1 and Table2. then I have a third tab which populates all its data based on INDEX MATCH Formulas, we can call this tab Lookup1.

I have made the table headers in Table1 and Table2 names ranges and in my Lookup1 tab, I want the table headers to be dropdowns from one of my dataset tables. I'd like to write the INDEX MATCH formula to change it's INDEX column if I changed the header title in my table.

is this possible without VBA? I have pasted the original formula below, the part colored in red is what I want to rewrite to reference the header of it's own column, match the column of the same name in the Table1 tab and insert that as the Index lookup. I believe I might need to nest in another INDEX and/or MATCH formula to do what I want, but I am not sure.

=IF([@[Employee ID]]"",INDEX(Table1[First Name],MATCH([@[Employee ID]],Table1[Employee ID]),0),"")

16. Bob Weigl says:

17. Bob Weigl says:

Forgot to mention the function - use IFERROR - wrap you INDEX...MATCH functions in IFERROR - and the last part of the function will be your default value (0 or "Not found - try again"). I don't like the #REF or #N/A answers either.

18. yaseen says:

hello

The results show me the same which type it
the results:- =INDEX((A2:D3, A5:D7), 3, 4, 2)

• Hello Yaseen,

This usually happens if a formula cell has a leading space or apostrophe before the equal sign; or if the Show Formulas mode is activated in the worksheet (Formulas tab > Formula Auditing). If neither is the case, please check out Excel formulas not calculating for other possible reasons and solutions.

19. Dakia says:

Hello,

I am trying to sum multiple columns that have months has my headers. When we close on another month data is updated in that column. I want to automatically sum the data in the rows without continually every month updating my sum formula at the end. Can someone please show me how to do this with Index and Match Function? I want it to be dynamic!

Thanks,

20. Gary says:

I'm trying to reference a cell in another workbook without success. I think my best way forward is to use Indirect() but have also tried Index() without success.
It seems to be failing on the external file location as such:
The entire cell reference is as such"
"http://apollo.omega.dce-eir.net/contentserverdav/nodes/3161757/[BIASDetailedVRFtracker.xlsm]alignment!\$D\$12"

My Indirect call is as follows:
Indirect("'http://apollo.omega.dce-eir.net/contentserverdav/nodes/3161757/[BIASDetailedVRFtracker.xlsm]alignment'!\$D\$12")

It is failing on:
http://apollo.omega.dce-eir.net/contentserverdav/nodes/3161757/

21. Srikanth says:

Hi,

How to set ascending order date format with using formula to another column.

Example: all dated A1:A20
Ascending another column like B1:B20

Thanks & Regards,
Sri

22. Tp says:

I am trying to use the index function to display a dollar amount listed in a table in the month that it will be billed for. I have multiple projects and when the formula is dragged down to the next project, the index gets off because the projects have different start dates. Is there a better way to have the index start at the first billing month other than copying the formula from the previous project to the first billing month of the next project?

Thanks.

23. Subba Reddy M says:

Hellow all,

I have two questions,please have a look and let me know is there anyway to find the solution,
1. I have two tables each having 37X12 rows and columns, First row and first column is having names for the corresponding data.Here the question is, i need to sort out column 1 data (which is having same names for two tables) from the reaming 35 columns of the two tables based on max, avg and min values. it is the combination of index, match for max min and avg, but no idea how to give the reference for two tables at a time to see max min and avg values for column data.

2. I have 15 matrices, each of the matrix is having 1833x1833 rows and columns. Here the data need to be sort out for specific rows and columns say for eg. between 500 to 1000. i need to sum these columns and rows by looking the sheet name and no idea how to specify the range to lookup and sum the data.

Looking for the solution,

Thanks, Subbareddy.

24. laxmikanta says:

A B C D E F G H
1/1/16 ALEX SONY 2/1/16 JOHN DELL ? ?
A to C and D to E are consecutive data categories.
I want to return in Cell G1 the name of the Person who made the sale at the latest date (JOHN).
And return in Cell H1 the name of the Brand for which the sale was done (DELL).
Kindly help.

25. laxmikanta says:

Date Name Brand Date Name Brand
are the entries.

26. Fred says:

I have a table with row 1 being dates from July thru to end of October and row 2 indicates whether a person is working (1) or not (0). I am trying to get my index/match formula to auto update to show how many days between today and the end of October the person is working. I have the following, but end up with #REF :-(

=sum(index(B4:CH4,2,match(today(),b4:ch4,0)):index(b4:ch4,2,85)

Thanks for any help

27. Fred says:

used a simpler approach, two SUMIFs

=sumif(B4:CH4,"="&Today(),B5:CH5)+sumif(B4:CH4,">"&Today(),B5:CH5)

gave me the answer I needed

I have to say your explanation and examples of the index function is the best I have seen.
Very clear and easy to follow.

Thank you so much!

Bruce

29. Nicholas says:

In Example 2 (Sum items between the specified two items)
you used the following formula
=SUM(INDEX(SourceData[Diameter],B1) : INDEX(SourceData[Diameter], B2))

is there a way i can add another condition? I want to only sum every second number in the range specified in the abovementioned formula?

so in your example 2, the answer will be 120,660+0+49,528+0 = 170,188

Thanks
Nicholas

30. lzbth says:

Hi,

I would like to create a inventory file with condition as follow.
1) multiple products/items in one worksheet as follow
2) inventory use first in first out method to calculate col F,G,H.

Is there any formula in column F, G & H that can automatic calculate.

B C D E F G H
1 Description/Cost/QtyIn/QtyOut/QtyBal/BalAmt/QtyOutAmt
2 Product A 10 416 416 \$4,160 \$0
3 Product B 20 400 400 \$8,000 \$0
4 Product A 200 216 \$2,160 200*10=\$2,000 [E4*C2]
5 Product B 250 150 \$3,000 250*20=\$5,000 [E5*C3]
6 Product A 12 150 366 \$3,960 \$0
7 Product B 50 100 \$2,000 50*20=\$1,000 [E7*C3]
8 Product A 300 66 \$792 (216*10)+(84*12)=\$3,168
9 Product C 15 200 200 \$3,000 \$0
10 Product B 22 200 300 \$6,400 \$0
11 Product A 50 16 \$192 \$600
12 Product C 15 100 300 \$4,500 \$0
13 Product B 150 150 \$3,300 \$3,100
14 Product C 150 150 \$2,250 \$2,250
15 Product A 10 300 316 \$3,192 \$0

31. jane says:

Hi Really struggling to source the correct formula required.

I have a table that has data entered including customer reference numbers and dates. the reference number can be entered several times in no particular order.

I want to lookup the reference number in one column, check that a date has been entered against all entries in another column and then return a Yes, No response.

Any help appreciated

32. Fatima says:

I need some help with the formula (combination of Index and Match)- If I have row ass Quarters (B2:G2) and business listed in Column B- How can add total for business by Quarter by just changing the Quarter name in AI cell.

33. Kishor Bhoir says:

Hi,

=INDEX(\$E2:\$E\$300,MATCH("PE",\$D2:\$D\$300,0))
=INDEX(\$E2:\$E\$300,MATCH("CE",\$D2:\$D\$300,0))

I am using above 2 formulas to extract data from E2:E300 for matching two words like CE and PE, which is situated in column D2:D300. I want result for PE in column F2 and drag down. Another CE result want in column G2 and down. But the CE word starts from row above 100 in column E and when I drag it in column G2 and down I am getting incorrect match. But getting correct result for PE.

34. MIKE F says:

Perhaps you can help me out. I am using the following array formula {=INDEX(ASSETP,SMALL(IF(STATP=\$A\$10,ROW(ASSETP)MIN(ROW(ASSETP))+1),ROWS(\$B10:B\$10)))}
to display vertically instances of asset #'s if they equal the status in cell A10. The formula works great. The cells in the "STATP" range are formulas to determine the status. =IF(K5="","L","X"). Later I realized that I needed to improve the status formula by checking for cost of the asset. I changed the status formulas to determine if cost was \$500.
=IF(AND(K6="",H6>499.99),"L",IF(AND(K6="",H6<500),"L2","X"))
The status formula works fine, but my array formulas no longer work the way I expected. If I put the Status formulas back to original the array formulas work as expected.

• MIKE F says:

SOLVED. When I changed the status formula one of the cells in the STATP range, gave an error as its result. This caused the array formulas to not work as expected. Once I corrected the error all array formulas worked as expected.

35. Harika says:

If I would like to sum the values of different individual columns based on row criteria then which formula should I use?

Example:
Name A1 Loc A2 Loc A1 Loc A2 Loc
ABC 5 7 8 5
XYZ 6 8 9 6
PQR 3 9 6 3

I want sum of XYZ values of A1 location

36. Anastasia Garbi says:

I am trying to retrieve the values from a range that match two criteria (A2, C2). I get only the first one, but I want them all in a dropdown list.

=INDEX(tbEmployee[Employee],MATCH(\$A\$2&\$C\$2,INDEX(tbEmployee[Country]&tbEmployee[Category],,),0),0)

What shall I do?

• Hi, Anastasia,

Try VLOOKUP function, it can return values based on few criteria.

37. william says:

Good day
i have a challenge . been working on a report card, but i can't go through :(
NAME ENG MATHS ADMA BIO GEO CIV PHY CHE AVG PSN
BRIAN 55 3 66 2 63 2 72 2 55 3 71 2 83 1 80 1 427 68 8
BKALUMO 65 2 52 3 0 FAIL 65 2 70 2 60 2 88 1 50 3 400 56 16
IAN 70 2 24 FAIL 36 FAIL 50 3 58 3 71 2 75 1 18 FAIL 360 50 22
TREVIS 88 1 54 3 51 3 80 1 70 2 77 1 90 1 72 2 477 73 3
GRIFFINS 68 2 26 FAIL 51 3 46 4 59 3 77 1 70 2 60 2 385 57 1 HARRISON58 3 12 FAIL 34 FAIL 54 3 55 3 71 2 75 1 40 4 353 50 25
ROSTEN 78 1 42 4 45 4 58 3 74 2 63 2 85 1 80 1 438 66 7
VICTOR 78 1 56 3 60 2 56 3 59 3 83 1 85 1 24 FAIL 421 63 9
GABRIEL 48 4 38 FAIL 33 FAIL 52 3 40 4 49 4 75 1 12 FAIL 302 43 28
The card works like this . a teacher will enter the mark for a pupil then it will calculate the grade ie 1-9(fail). and also calculates the best 6 subjects including english as shown in the 3rd last column.(SUM(LARGE(E3:Q3,{1,2,3,4,5}))+C3)

i want to now add a column that will calculate the points (sum of best grade). i have tried alot of formulars but the challenges comes because the grades where found by a formular and are not in a range (in different columns)
=SUM(SMALL(range,{1,2,3,4,5}))+M34 - this formular isnt giving an answer but an error ### #num!. am stuck on how to go about it. i noticed it could be because the grades for a particular pupil are in different columns and are a result of a formular.

38. Enayat says:

Hello,
I have a list with 2 columns:
A:text
B:number
I wanted to
1- select records that have value 'USD'in column A (This outputs a range include recores that value of column A is 'USD')
2- Sum the column B values on range which specified in previuse step.
HOW TO DO THIS IN EXCEL USING FUNCTIONS?
thanks before

39. Aker says:

Hello,
I have two columns (A and B) and that they are reference columns. I need to get the columns (C and D) are sorted according to the ref. column B using INDEX and/or MATCH functions. For instance:
No. B C D
= = = =
1 A 2 4
2 C 1 2
3 E 3 1
4 C 4 3
After sorting according to column B the columns C and D it becomes below:
No. B C D
== = = =
1 A C E
2 C A C
3 E E C
4 B C A
How to do this using functions (not normal or custom sorting) in Excel?
Thank you so much in advance.

40. awaz says:

Hi There
I have an employee data base, i have to send every week the manpower list each dept.

i want to create the drop down list by dept. when i select any dept my worksheet should the employees names in that dept.

41. kam says:

Estimated. Sq. Ft Estimated Capex Spend (£)

2,000 185,000
3,500 436,000
5,000 660,000

how do i find whats estimated capex spend for example 2500 sq ft based on the data above. what formula could i use to get an estimate?

42. Jim says:

I have a schedule for 10 teams playing on 5 boards. They play 3o days and play 2 games each day, against different teams.
Left column are the dates, then to the right is what teams play each other and the board they play on above the teams.
I would post the schedule but do not see where I can post it.

I presently have a chart that I populate manually and want to do automatically depending upon what the date is, whether the 1st or 2nd game is being played.
I tried index, match, vlookup and cannot get any of them to work with the schedule I have.
What do you suggest I do? Hopefully I explained what I'm trying to do.
Thanks,
Jim

43. Jennifer says:

I am building a task list that needs to populate a "dashboard". There is a row in each task labeled "launch" that has a date associated with in the neighboring column. Then in the same column as "launch" there are a list of activities that has a list of corresponding "date completed". The dashboard has columns that match the activity title and the rows have the matching campaign. I'm currently using this function: =IF(ISERROR(INDEX(ALLCAMPS,SMALL(IF(ACTIVITY=\$R\$1,ROW(Complete_Date)),ROW(1:1)),12)),"",INDEX(ALLCAMPS,SMALL(IF(ACTIVITY=\$R\$1,ROW(Complete_Date)),ROW(1:1)),12)) with delete + shift + enter. It logs down my system. Is there a better way to do?

• Hello,

=IFERROR(INDEX(ALLCAMPS,SMALL(IF(ACTIVITY=\$R\$1,ROW(Complete_Date)),ROW(1:1)),12),"")

If this doesn’t work either, I’m afraid you’ll need to use a special macro then. We can’t help you with this since we do not cover the programming area (VBA-related questions). In this case please try to find the solution in VBA sections on mrexcel.com or excelforum.com.

I do hope you’ll manage to solve this task!

44. Andy says:

Am struggling to understand how to construct a formula that populates the "grades" column below by reading the "rounded" column content and returning the value in the "Grade" column if the value in the "rounded" column is equal to or greater than the value in the "From" and is equal to or less than the value in the "To" columns.

Any help appreciated:

165,000 ?
120,000 ?
110,000 ?
100,000 ?
100,000 ?
90,000 ?
90,000 ?
88,580 ?
85,000 ?
80,000 ?

160,000 170,000 A1
150,000 159,999 A2
140,000 149,999 A3
130,000 139,999 A4
120,000 129,999 A5
110,000 119,999 A6
100,000 109,999 A7
90,000 99,999 A8
85,000 89,999 B1
80,000 84,999 B2
75,000 79,999 B3
70,000 74,999 B4
65,000 69,999 B5

45. shanupandey says:

how can i use index formula. in multiple table in one sheet

46. JANE says:

Hi,

I want to write a VBA function making use of LINEST excel function to calculate some tstats for the slope. I know that i have to combine the INDEX function with the LINEST function, but i don't know how. Can anyone help?

47. JANE says:

Hi,

I want to write a VBA function making use of LINEST excel function to calculate some tstats for the slope. I know that i have to combine the INDEX function with the LINEST function, but i don't know how. Can anyone help?

Index match on multiple criteria with multiple sheets

i have a workbook with 4 sheets 1st three are Jan, Feb, Mar and 4th one is home. Columns are style no., qty and unit price. i want to check index match the unit price of mentioned style number from all three sheets. Like i want a formula in HOME sheet in cell C2 which see style no. in cell A2 and then check it's unit price in all three sheets and put unit price of matched style no.

Three sheets Jan, Feb, Mar are like this.

Style No. Qty Unit Price
WTC123456 456 45,000
WTB281654 1000 65,000

HOME Sheet.

Style No. Qty Unit Price
WTC123456 456 here i want formula
WTB281654 1000

because some styles are put in Jan and some are in Feb so on. but home sheet has all the styles no.

so actually i have 12 sheets of 12 months
Thanks

49. Abtahei says:

Hi,
can we use INDEX to select the default value of a list in data validation?
for example in below list
A1 Select one
A2 AA
A3 BB
A4 CC

by default, the drop list in D1 which is created by data validation >list
shows select one/

50. Saroj says:

Hi,
I have seen videos to populate pivot based on the value selection in the drop down using Index and Match function. My data is tricky not able to replicate the same issue. Below are the columns of info I have in the spread sheet:

Dept Month Target Actual Difference
Mktg Jan-18 100 80 20
Mktg Feb-18 120 118 2
Mktg Mar-18 90 94 4
TeleM Jan-18 200 210 10
TeleM Feb-18 150 148 2
TeleM Mar-18 110 103 7

I want my chart to change dynamically. Could you please help how to write the Index Formula. I want to display the chart data by for selected period by dept. I will select the dept and then the range of the periods which will display the chart. Since I have multiple times dept and month are coming in the data spreadsheet it is not giving the right result.

51. Suzanne says:

Hi,
Could you pls show me how to find the median value of below case:
Q1: How to find the Median of STAFF & SUPVR grade staff's salary?
Q2: How to find the Median of MGR & ADIR grade staff's salary?

A B
1 STAFF 100
2 SUPVR 120
4 STAFF 150
5 MGR 200

I could make it work by using below formula...
=MEDIAN(IF(A1:A5="STAFF",IF(A1:A5="SUPVR",B1:B5)))
=MEDIAN(IF((A1:A5="STAFF")*(A1:A5="SUPVR"),B1:B5))

Thank you for the support.

• Doug says:

Suzanne:
Where the employee titles are in G3:G8 and the salaries are in H3:H8 enter this in H10:
{=MEDIAN(IF(G3:G8=G10,H3:H8))}
Then in G10 enter one of the employee titles and the median salary for that title should be displayed in H10.
Note the curly brackets around the formula. This indicates to Excel that this is an array. After you enter the formula in the formula bar, put the cursor in the formula and click CTRL Shift Enter and you should see the curly brackets around the entire formula. When entered in this manner Excel will treat the formula as an array.
You can change the addresses and ranges to suit your needs, but when you change something in the formula you need to put the cursor in the formula and click CTRL Shift Enter again to get the array back.

52. Peter says:

Hi,

I have a workbook with roughly 40 identical sheets of data. I use the following formula to draw this data to a dashboard dependent on the sheet name.

=+SUM(INDIRECT("'"&B2&"'!\$H\$130:\$H\$141"))

How can I change the formula to automatically update if a row is inserted above the data that is being referenced? i.e the data set effectively moves down by 1 so that formula would have to read as:

=+SUM(INDIRECT("'"&B2&"'!\$H\$131:\$H\$142"))

53. Eric says:

Hi, I have the following table:

10/6/18 11/6/18 12/6/18 13/6/18
jan 5 3 2
tom 3 8 1
anna 1 1 1

I would like to get one formula where I can look up a name than find the n-number in that row and return the date correspondent with that n-number.

eg. find anna, 2 number = 11/6/18
or find tom, 1 number = 11/6/18
or find tom, 3 number = 13/6/18

thanks

54. Eric says:

Hi,
I have the following table ("x" represent blank cell)

date: 10/6/18 11/6/18 12/6/18 13/6/18
Jan 5 x 3 2
Tom x 3 8 1
Anna 1 1 x 1

I would like to get one formula where I can look up a name than find the n-number in that row and return the date correspondent with that n-number.

eg. find anna, 2 number = 11/6/18
or find tom, 1 number = 11/6/18
or find tom, 3 number = 13/6/18

thanks

55. Mike says:

Hello, I hope you are still following these responses. I'm trying to process some autosomal DNA results, by identifying where segments are matching against the full results table 720450 rows long, from a table telling me the Chromosome and the start and stop locations of the matching sections. This identification process is to be used in a process called phasing where I need to extract actual results to form the basis of reconstructed kits. I can do this process manually by writing a 0 against all non matching sections, and in the same column 1 for the rows within each matching section. I have used the INDEX (.. Match... ) ) combination before but I can't work out how to proceed in this case. The [Segmeant ID] column is where I want the out come to be recorded. In words - For the matching segments in chromosomes 1-22 each with a start and end location, look up in the [position] column (720450 rows long) and write a [1] in the [Segment ID] column for all locations within a matching segment and [0] when there is no matching. I have hidden other columns with other relevant information, so as not to confuse the question. I though I could use a simplified IF and AND functions, but I now realized this is a better approach. I could send an Excel sheet if that helps understanding the requirement.
RSID CHR POSITION RESULT Segment ID Chromosome Start Location End Location
rs4477212 1 82,154 -- 0 1 15,359,967 41,402,143
rs3094315 1 752,566 -- 0 1 50,056,542 153,312,871
rs3131972 1 752,721 -- 0 1 163,093,382 201,166,817
rs12562034 1 768,448 -- 0 1 214,057,330 237,565,188
rs12124819 1 776,546 -- 0 2 11,944 220,002,834
rs11240777 1 798,959 -- 0 3 122,014,566 192,758,191
rs6681049 1 800,007 -- 0 4 63,814,811 91,516,422
rs4970383 1 838,555 -- 0 4 158,857,135 190,937,862
rs4475691 1 846,808 -- 0 5 19,760,858 107,011,958
rs7537756 1 854,250 -- 0 5 147,717,357 180,236,547
rs13302982 1 861,808 -- 0 6 155,815 27,216,699
rs1110052 1 873,558 -- 0 6 114,752,803 170,919,470
rs2272756 1 882,033 -- 0 7 43,748 8,714,115
rs17160698 1 887,162 -- 0 7 18,831,265 129,380,647
rs3748597 1 888,659 -- 0 7 148,038,351 155,812,421
rs13303106 1 891,945 -- 0 8 164,984 40,954,561
rs28415373 1 893,981 -- 0 8 63,335,905 146,299,857
rs13303010 1 894,573 -- 0 9 46,587 4,501,492
rs6696281 1 903,104 -- 0 9 80,127,963 104,550,554
rs28391282 1 904,165 -- 0 10 95,844 8,855,244
rs2340592 1 910,935 -- 0 10 108,879,609 134,066,136
rs13303118 1 918,384 -- 0 11 194,062 25,120,833
rs2341354 1 918,573 -- 0 11 80,789,029 134,945,120
rs6665000 1 924,898 -- 0 12 86,300 76,348,112
rs2341362 1 927,309 -- 0 12 130,093,883 133,838,353
rs9777703 1 928,836 -- 0 13 19,020,095 115,106,996
rs1891910 1 932,457 -- 0 14 32,733,284 88,510,255
rs9697457 1 934,345 -- 0 15 20,004,966 29,581,108
rs35940137 1 940,203 -- 0 16 1,079,531 85,959,438
rs3128117 1 944,564 -- 0 17 19,519,444 43,147,351
rs2465126 1 947,034 -- 0 17 58,240,764 81,044,305
rs2341365 1 948,692 -- 0 19 4,458,063 46,367,703
rs15842 1 948,921 -- 0 21 9,922,018 25,935,761
rs6657048 1 957,640 -- 0 22 16,055,122 51,214,796

56. Kailey says:

I'm using and index function to sort names into different sheets based on one master sheet that has drop downs. For example: all the names will be on one sheet and then there will be a drop down list for their group and their name will go that corresponding sheet. My issue right now is that it includes all groups below it. So the "Red" group will have red, blue and yellow in the sheet. The blue group will have blue and yellow in the sheet. How do I have it stop with only the specific color?
=IFERROR(INDEX(Personnel!\$A4:\$A\$400, MATCH(0, IF("Blue"=Personnel!\$B\$4:\$B\$400, COUNTIF(\$CW\$1:\$CW1, Personnel!\$A4:\$A\$400),""),0)),"")

Data Table (Location and Holiday Dates)
Loctn Holidays
SPD 01-Jan-19
SPC 01-Jan-19
SPM 01-Jan-19
SPK 01-Jan-19
SPB 01-Jan-19
SPD 14-Jan-19
SPC 14-Jan-19
SPM 14-Jan-19
SPK 14-Jan-19
SPC 15-Jan-19
SPB 15-Jan-19

Working sheet, need the days between the two dates.
Loctn Start Date End Date No of Days No of Days
SPD 31-Dec-18 30-Jan-19 =networkdays.INTL(L2,M2,11,XXX) 25.00
SPC 31-Dec-18 30-Jan-19 =networkdays.INTL(L2,M2,11,XXX) 24.00
SPM 31-Dec-18 30-Jan-19 =networkdays.INTL(L2,M2,11,XXX) 25.00
SPK 31-Dec-18 30-Jan-19 =networkdays.INTL(L2,M2,11,XXX) 25.00
SPB 31-Dec-18 30-Jan-19 =networkdays.INTL(L2,M2,11,XXX) 25.00

58. krishna says:

i need to call cells from another tab
my reference cell consist of a name Jhon
but there are multiple results for jhon & I want to call all of them in a single tab, please mention the formula i could use.
Colum-1, Column-2
eg: John-----4458
john-----4459

59. hashini says:

What will the following formula return?

=INDEX(A5:H448,5,6)

60. Care29 says:

Hi can you help me with my problem here.. it seems my index match formula is correct but I can't make it work somehow.. I have 3 criteria's to consider to get the correct data I need.

• Care29 says:

It works if I only use 1 match with the index, but I have to use two more match formula to consider the other two criterias....

61. Hayati Erden Gurel says:

Hello;
My problem is either very simple or not possible. But, I could not find solution.
I have data like :
1,one,2,two,3,three,4,four,5,five etc.....
When i put these data to cells, I am achieving my target through vlookup, Match, etc.
I want to finish whole formula in 1 cell, without referring any other cell. i.e. These data should come as constant.
OR,
Please tell me how can i increase number of content of formula in a cell from 8192 to 12000 ???

Thank you,
Best Regards,

62. KY says:

I have a large sheet and want to calculate the attendance of each student for a specific period. For example, 1=present and 0=absence, I want to sum each student present from date10 to date80. How?
Date : date1, date2, date3, date4 ... date99, date100
student1, 1,1,0,1 ... 1,1
student2, 1,0,1,0 ... 0,1