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 365 - 2003.

INDEX array form

The INDEX array form returns the value of a certain element in a range or 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:
An example of the INDEX array form

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).
An example of the INDEX reference form

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 source table to be used in formulas

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:
An INDEX formula to get the N<sup>th</sup> item from the list

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:
An INDEX formula to get the value at the intersection of a given row and column

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.
A formula to calculate the average of values in a list

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))
Calculating the average of the top N items in the list

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))
Sum values between the first and the last item you specify

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.
INDEX formula to create a dynamic named range

Tip. The easiest way to create an expandable dynamic data validation list is dropdown from 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.
The INDEX / MATCH formula for a left Vlookup

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:
The named ranges to be use in the INDEX formula

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.
The INDEX formula to get one range from a list of ranges

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:
A user-friendly INDEX / IF formula to get 1 range from a list of ranges

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!

Table of contents

118 comments

  1. I have to select 50 random invoices to review for a cyclical audit. I've added all the invoice numbers (so far) into Column A of an Excel spreadsheet. More will be added as time goes on, and we'll have to select from all of them for each audit. Cell A:1 has a text header ("Invoice#").

    If I could give Excel instructions in English, I'd say, "Give me a random invoice number from all the cells in Column A which contain data, but don't select the header cell (A:1)."

    Here's what I came up with: =INDEX(A:A,RANDBETWEEN(2,COUNTA(A:A)))

    With some brief testing, that seems to be "good enough" but, since it's for an audit and I only half understand how the formula works, maybe you could confirm? FYI, I don't mind having to drag the formula down to get as many results as I need.

  2. Hello I use index (=index(‘name’!$A:$A,Row(A3)-1) this data from table contains names and locations (not numeric) but I have date, I made a formula can changing the data by a scroll bar monthly this formula in column name monthly check (true or false) =and([@date]>=calculations!$c$1,[@date]<calculations!$c$1+31)

    I try to use to combine the index formula with (tablename[monthlycheck]=true) it didn’t works
    How can i combine for these two ?? Please need your help

    Thank you in advance

    • Hi! Your task is not completely clear to me. Please clarify your specific problem or provide additional details to highlight exactly what you need. Write an example of the source data and the result you want to get.

      • Hey,
        I have 3 sheets names “data”,“calculation” and “dashboard”.
        Data sheet for data entry filling by the headers “branch, problem, date, notes”
        ( this data not numeric data its alphabetics data except date) and i made on data sheet column named “monthly check” using formula =and([@date]>=calculations!$c$1,[@date]<calculations!$c$1+31) to show me (true or false)
        (c1 in calculations sheet linked to scroll bar on the dashboard to change the data on monthly bases)
        In the calculations sheet i have made a formula =index(‘branch’!$A:$A,Row(A3)-1) for each header (branch, problem, notes) to organize this data on the dashboard sheet.
        My question what is the formula so I can add it with the index formula above then I can change data on the dashboard by the the scroll bar on monthly bases
        Thank your help.

          • The result what i need it , I made a scroll bar in the dashboard sheet that allow me to change the data by monthly bases when i click on the scroll bar, but the issue is on the calculation sheet , I want to extract the data from data sheet ( I want to extract the data from the data sheet as a alphabetics data not numeric)

  3. Hello

    i am trying to create an index formula as follows =INDEX(TABLES!D98:S189, MATCH(1, ($C$3="TABLES!$D$97:$S$97") * ($C$4="TABLES!$C$98:$C$189") * ($C$5="TABLES!$B$98:$B$189") * ($C$6="TABLES!$A$99:$A$189"), 0))

    i am always getting either #value or #N/A error , i don't know why and where is the problem , can you help pls -thank u

    i created tables in a another sheet called tables which includes the below (i can't send you the percentage table as it 's big)
    YEAR 2008 VALUE $10,000.00 CATEGORY Private COMPANY AMANA PLAN AMANA-Product A
    2009 $11,000.00 BUS ARABIA AMANA-PRODUCT B
    2010 $12,000.00 TAXI UFA ARABIA-SUPREME
    2011 $13,000.00 MOTORCYCLE ARABIA-ELITE
    2012 $14,000.00 HYBRID ARABIA-LEGACY
    2013 $15,000.00 VAN & PICH UP UFA-STANDARD
    2014 $16,000.00 UFA-LUX
    2015 $17,000.00 PRODUCT A
    2016 $18,000.00
    2017 $19,000.00
    2018 $20,000.00
    2019 $21,000.00
    2020 $22,000.00
    2021 $23,000.00
    2022 $24,000.00
    2023 $25,000.00
    $26,000.00
    $27,000.00

    • Hi! All ranges of values that you multiply in the MATCH function must be of the same size. ($C$5="TABLES!$B$98:$B$189")*($C$6="TABLES!$A$99:$A$189") is incorrect. Besides, note that in the INDEX function you extract a row, not a single value.

  4. Hi Everyone!

    I was trying to retrieve entire row value (like spill feature of xlookup) with index match function , but always shows #value error. whats the simple way for this? or any other complex solution?
    please tell me syntax

  5. i have a data where i have these columns,
    1. month, (july-jun)
    2, Employee No. (each employee number is 12 times in this column)
    3. employee name
    4, basic pay
    5. conveyance allowance
    6. income tax
    7. other deductions
    8. office allowance
    i want to generate a slip, where i just enter an employee code, so i get his month wise each head payment. how to use

  6. Hi,
    I have a different problem. In column A i have some list of values. I am using formula =INDEX(A:A, MATCH(LARGE(A:A,1, A:A,0)) to get first top value, and by operating "k" argument of Large function to get second top, and third.
    But how to rank list if values are negative and positive, and from list f.ex.( 100, 20, 8, 5, 3, -2, -4, -60) as a top three get (100, -60, 20) ?
    Speeking differently i need top three values with highest "distance" from zero.

    • I got it now :)
      Sorry for spamming.
      formula will be like: {=INDEX(A:A, MATCH(LARGE(ABS(A:A),1, ABS(A:A),0))}

    • Hi! To work with absolute values of numbers, use the ABS function.

      =INDEX(A:A, MATCH(LARGE(ABS(A1:A1000),1, ABS(A1:A1000),0))

      Also note that using whole-column references (A:A) in formulas can slow down calculations.

  7. I have list of room numbers including area, which level they are, room area etc. Each room is assigned to a particular AC unit. Now I need another sheet where I want to list those rooms that assigned to a particular AC unit. I tried using the example above but with no luck.

  8. Hello sir,
    I want to index names of persons whose selles value is more than a certain value in another colomn.

    P1 500
    P2 50
    P3 1000
    P4 40
    P5 90
    P6 200
    P7 80
    P9 150
    P10 300

  9. Very powerful stuff. I use index match all the time, however, needed advanced use of these two functions. This blog helped tremendously. Thank you thank you thank you.

  10. I am trying to use Index and match to pull a single value from a date range on one tab to another tab in the same spreadsheet based on a date that is < = the current date.

    INDEX(Handicap!AP2:AP106,(MATCH("<="&TODAY()-365,Years!AU2:AU106,0)))

    AP2:AP106 is the date range
    AU2:AU106 is the value I am trying to pull

    Keep getting #N/A

    • Hello!
      To find the first date that is greater than or equal to the specified date, use this example.

      =MATCH(TRUE,(TODAY()-365<=D1:D10),0)

      • Thanks for the reply...i am not only trying to find the date but i am also trying to get a number that is in another cell that corresponds to that date.

        • I got this to work: =INDEX(Years!AU2:AU106,MATCH(TRUE,(TODAY()-365<=Years!AP2:AP106),0))

          today is 2.24.23...this formula works like you said and is returning the first date greater than 2.24.22 which is 3.1.22....however i want it to return the first date that is less than 2.24.23 which in the spreadsheet is 2.19.22

          • Hi!
            The formula I sent to you was created based on the description you provided in your first request. Change the sign < to > in the formula

  11. Thanks - great article, struggling with INDEX function

    and need some help

    I have a number of closed excel workbooks
    I have a consolidation workbook that should look up values from the closed workbooks placed in different folders

    I can manually enter a index function to make the look work - but I need the function to be automatic based on some cell values in the consolidation workbook.

    e.g.
    =INDEX('C:\Desktop\PE\PE_1130\[PE1130.xlsx]PE'!C:C;3;1) this works manually entering

    but the reference should be dynamic like this example using cell value c6 to create the path
    ="'C:\Desktop\PE\PE_" &C6& "\[PE"&C6&".xlsx]PE'!C:C;3;1"

    But I cannot figure out how to get this into the INDEX function?

    Is that possible???

    TIA

  12. My bad.
    I meant to thank you, Svetlana.

  13. Hi Yaseen,
    First of all, thank you and kudos for posting this wonderful excel article.

    Is there a way to build a dynamic drop-down list into a cell that is built from a table wherein column 1 has the condition and column 2 needs to fetch the result into the drop-down?

    Eg:
    In Sheet 1, I have A1 where I select Technical so the B1 cell should populate the dropdown with the values from Table1 in Sheet 2 which has two columns where Col1 has values Technical and Commercial while Col2 has several entries for Technical and Commercial.

    I hope you get the picture.

    Thanks

  14. Hi

    Is there a way to have a total & use a minus range?
    This is the formula to give the total of the minus {=SUM((AQ808=AU$789)*V808:AA808)}
    The total & minus equation is AS808 & V808:AA808

    My question is how to place a total & minus in the formula?
    Plus can the formula cover blank cells?

    Regards

    Tony O'Brien

      • Hi Alexander

        To minus up to 8 cells, some may be blank from a total or balance.

        =SUM((AQ808=AU$789) = two rules to match total

        AS808 = total cell

        V808:AA808 = minus range that may have blank cells

        Regards

        Tony O'Brien

        • Hi!
          The information you provided is not enough to understand your case and give you any advice, sorry. What is "minus range"? Give an example of the results you want.

  15. =SUMPRODUCT(('Journal-Jan21'!C5:C5519='DASH BOARD'!C9)*('Journal-Jan21'!D5:D5519≠ 'DASH BOARD'!D9), 'Journal-Jan21'!F5:F5519)

    Why it is not working.
    In my data column "C" contains VOUCHER TYPE and column "D" contains CONTROL ACCOUNT HEAD and "F" is value. In column "D" which has multiple ACCOUNT HEADS, I need their total except of one Account Head, I mentioned as ≠ (not equals to) in D9 result is appearing #NAME#. Please guide me and rectifiy my formula

      • Your one tip solved my problem.

        Stay Blessed

        Best Regards,

        Muhammad Rizwan

  16. Ablebits,

    I think you all are the best at explaining the functions that I have seen and I really appreciate the tips and notes

    In the "Example 1. Calculate average of the top N Items" section, I'm having a difficult time getting the formula "=AVERAGE(C5: Index(SourceData[DIameter], B1)) to work. I have defined "SourceData" correctly but I'm having trouble with the "[Diameter]" piece. Is Diameter a named range that consists of all (including the word "Diameter") values in the Diameter column? Is Diameter a named range that includes only the values in that column? I have tried both ways and neither way works. Also, does Diameter have to enclosed with those []? I'm baffled. Can you help?

    Again, I love this website and have learned a lot from you all.

    Thanks!

    • Hello!
      Diameter is a named range that includes only the values in that column. Also, pay attention to the correct spelling "Diameter" in your formula.

  17. I have worksheet and that have multiple formula and function
    I will give you example

    =INDEX(tab28,SMALL(IF(($F$1='Compressive Data Worksheet'!$A$9:$A$571),ROW('Compressive Data Worksheet'!$A$9:$A$571)-MIN(ROW('Compressive Data Worksheet'!$A$9:$A$571))+1,""),ROW(D2)),25)

    Someone can help me to crack down this formula please

    • Hi!
      I can't check the formula that contains unique references to your workbook worksheets, sorry.
      What result do you want to get exactly?

      • what is Index ( tab 28)
        I know tab 28 is reference but I dont know how to change my reference

  18. 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

  19. 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,

  20. 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.

    • 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....

  21. What will the following formula return?

    =INDEX(A5:H448,5,6)

  22. 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

  23. 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

  24. 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)),"")

  25. 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

    Please can someone help me.
    thanks

  26. 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

    Please can someone help me.
    thanks

  27. 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"))

  28. 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
    3 ADIR 500
    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.

    • 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.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)